如何從該表中獲取date_departure并date_arrival根據每次旅行visiting_order
select * from step;
id_step | id_travel | id_port | visiting_order | date_arrival | date_departure
--------- ----------- --------- ---------------- -------------- ----------------
1 | 1 | 1 | 0 | | 2021-01-12
2 | 1 | 2 | 1 | 2021-05-20 | 2021-05-22
3 | 1 | 3 | 2 | 2021-07-27 |
4 | 2 | 4 | 0 | | 2021-02-13
5 | 2 | 5 | 1 | 2021-02-27 |
6 | 3 | 7 | 0 | | 2022-01-12
7 | 3 | 6 | 1 | 2022-05-27 |
像這樣 ：
id_travel | date_departure | date_arrival
------------ ---------------- --------------
1 | 2021-01-12 | 2021-07-27
2 | 2021-02-13 | 2021-02-27
3 | 2022-01-12 | 2022-05-27
?
我的第一個意圖是把兩列都拿來合并
(SELECT id_travel, date_departure FROM step WHERE visiting_order = 0
GROUP BY id_travel, date_departure)
UNION
(SELECT A.id AS id_travel, A.arr_date AS date_arrival FROM
(SELECT id_travel, MAX(visiting_order), date_arrival
FROM step GROUP BY id_travel
) AS A(id, ord, arr_date)
);
第一次選擇沒問題
id_travel | date_departure
----------- ----------------
1 | 2021-01-12
2 | 2021-02-13
3 | 2022-01-12
但第二個回傳錯誤
ERROR: column "step.date_arrival" must appear in the GROUP BY clause or be used in an aggregate function
uj5u.com熱心網友回復：
似乎這可以是：
SELECT id_travel
, min(date_departure) AS date_departure
, max(date_arrival) AS date_arrival
FROM step
GROUP BY 1
ORDER BY 1;
當然適用于您的示例資料。
uj5u.com熱心網友回復：
SELECT DISTINCT step.id_travel, sub_query1.date_departure, sub_query.date_arrival
FROM step
INNER JOIN
(SELECT step.id_travel, MAX(step.date_arrival) as date_arrival
FROM step
GROUP BY step.id_travel
)
AS sub_query ON (sub_query.id_travel = step.id_travel)
INNER JOIN
(SELECT id_travel, date_departure FROM step WHERE visiting_order = 0 GROUP BY id_travel, date_departure)
AS sub_query1 ON (sub_query1.id_travel = step.id_travel)
ORDER BY id_travel;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482502.html
標籤：sql PostgreSQL 加入 通过...分组 聚合函数
上一篇：SQLServer分組資料
下一篇：查詢同一張表以創建搜索螢屏的排序