我有 2 張桌子。第一個包含某些機場的 ID，第二個包含從一個機場到另一個機場的航班。
ID Airport
---- ----
12 NYC
23 LOS
21 AMS
54 SFR
33 LSA
from to cost
---- ---- ----
12 23 500
12 23 250
23 12 200
12 12 50
12 12 90
21 23 100
54 12 400
33 21 700
我想回傳一個表，其中僅包含可互換的總成本的機場。
期望的輸出：
airport_1 airport_2 total_cost
---- ---- ----
NYC LOS 950
NYC NYC 140
uj5u.com熱心網友回復：
您可以使用以下查詢
select max(a1.Airport)Airport_1,min(a2.Airport)Airport_2,sum(cost)total_cost
from
(select *
from flights
where [from] in (select ID from airports where Airport = 'NYC' or Airport = 'LOS')
and [to] in (select ID from airports where Airport = 'NYC' or Airport = 'LOS')) t1
join airports a1 on t1.[from] = a1.ID
join airports a2 on t1.[to] = a2.ID
group by
case
when
(a1.Airport = 'NYC' and a2.Airport = 'NYC')
then 0
when
(a1.Airport = 'NYC' and a2.Airport = 'LOS')
then 1
when
(a2.Airport = 'NYC' and a1.Airport = 'LOS')
then 1
end
結果
uj5u.com熱心網友回復：
WITH AIRPORTS(ID,AIRPORT)AS
(
SELECT 12, 'NYC' UNION ALL
SELECT 23, 'LOS' UNION ALL
SELECT 21, 'AMS' UNION ALL
SELECT 54, 'SFR' UNION ALL
SELECT 33, 'LSA'
),
DESTINATIONS(FROMM,TOO,COST)AS
(
SELECT 12, 23, 500 UNION ALL
SELECT 12, 23 , 250 UNION ALL
SELECT 23, 12, 200 UNION ALL
SELECT 12, 12, 50 UNION ALL
SELECT 12, 12, 90 UNION ALL
SELECT 21, 23, 100 UNION ALL
SELECT 54, 12, 400 UNION ALL
SELECT 33, 21, 700
),
UQ AS
(
SELECT GREATEST(D.FROMM,D.TOO)AS FLAG_1,LEAST(D.FROMM,D.TOO)AS FLAG_2, SUM(D.COST)AS TOTAL_COST,
COUNT(*)AS CNTT
FROM DESTINATIONS AS D
GROUP BY GREATEST(D.FROMM,D.TOO),LEAST(D.FROMM,D.TOO)
)
SELECT A.AIRPORT,A2.AIRPORT,Q.TOTAL_COST
FROM UQ AS Q
JOIN AIRPORTS AS A ON Q.FLAG_1=A.ID
JOIN AIRPORTS AS A2 ON Q.FLAG_2=A2.ID
WHERE Q.CNTT>1
對查詢的一些小注釋： AIRPORTS 和 DESTINATIONS 是測驗資料的除錯表示。主要作業在公用表運算式（CTE）UQ 中完成。這里的想法是以通用形式呈現“可互換”的飛行以執行所需的計算。它是通過使用 GREATEST AND LEAST 函式完成的 最后一個 SELECT 是顯示所需的輸出
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413598.html
標籤：