我有 2 張桌子。第一個包含某些機場的 ID，第二個包含從一個機場到另一個機場的航班。
ID Airport
---- ----
12 NYC
23 LOS
21 AMS
54 SFR
33 LSA
from to cost
---- ---- ----
12 23 500
23 12 200
21 23 100
54 12 400
33 21 700
我想回傳一張表，其中僅包含在這種情況下可互換的機場（NYC -LOS），總成本。
期望的輸出：
airport_1 airport_2 total_cost
---- ---- ----
NYC LOS 700
uj5u.com熱心網友回復：
LEAST()通過使用和GREATEST()函式以及子句，您可以在不需要子查詢的情況下獲得結果，HAVING例如
SELECT MIN(airport) AS airport_1, MAX(airport) AS airport_2, SUM(cost)/2 AS total_cost
FROM flights
JOIN airports
ON id IN ("from" , "to")
GROUP BY LEAST("from","to"), GREATEST("from","to")
HAVING COUNT(DISTINCT "from")*COUNT(DISTINCT "to")=4
其中每對（2）被計算兩次（2）->（2 * 2 = 4）
Demo
uj5u.com熱心網友回復：
你可以試試這個：
select c.Airport as airport_1,
d.Airport as airport_2,
(select (select cost from Table2 where "from" = a."from" and "to" = b."from")
(select cost from Table2 where "from" = b."from" and "to" = a."from")) as cost
from Table2 a inner join Table2 b
on a."from" = b."to" and a."to" = b."from" and a."from" < b."from"
inner join Table1 c on a."from" = c.ID
inner join Table1 d on b."from" = d.ID
小提琴
基本上，您將第二個表與自身連接以查找“可互換”的機場，然后將結果與第一個表連接兩次以獲取機場的名稱。使用子查詢獲取總成本
uj5u.com熱心網友回復：
基本上自我加入具有互換出發地和目的地的航班表，然后離開加入機場以獲取他們的名字。
SELECT a1.name AS airport_1,
a2.name AS airport_2,
f1.cost f2.cost AS total_cost
FROM flight AS f1
INNER JOIN flight AS f2
ON f1."from" = f2."to"
AND f1."to" = f2."from"
AND f1."from" <= f2."from"
LEFT JOIN airport AS a1
ON a1.id = f1."from"
LEFT JOIN airport AS a2
ON a2.id = f1."to";
db<>小提琴
uj5u.com熱心網友回復：
我懷疑為了清楚起見，可能需要一些更多樣化的樣本資料，但是看看以下內容是否適合你。首先識別與目的地匹配的航班，然后按這些劃分并對每個磁區視窗的成本求和，回傳每個匹配的第一個：
with m as ( /* Find flights with matching from/to */
select *,
case when exists (
select * from flights f2
where f2."from"=f."to" and f2."to"=f."from"
) then least("from","to") else 0 end m
from flights f
), c as ( /* partition by matches and sum cost */
select *, Row_Number() over(partition by m order by "from") rn,
Sum(cost) over(partition by m) total_cost
from m
where m >0
)
select afrom.airport airport_1, ato.airport airport_2, total_cost
from c
join airports afrom on afrom.id=c."from"
join airports ato on ato.id=c."to"
where rn=1;
示例小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413612.html
標籤：