sql - 计算每个目的地的乘客
问题描述
我正在尝试计算前往每个目的地的乘客人数。这是一些示例代码,如果有帮助的话。
DDL:
CREATE TABLE #PAX
(
FLIGHT_NO INT,
SEG INT,
DEP VARCHAR(3),
ARR VARCHAR(3),
First_Class INT,
Economy INT
)
DML:
INSERT INTO #PAX(FLIGHT_NO, SEG, DEP, ARR, First_Class, Economy)
SELECT 220, 1, 'STL', 'JFK', 11, 166
UNION ALL
SELECT 220, 2, 'STL', 'DXB', 13, 21
UNION ALL
SELECT 220, 3, 'JFK', 'DXB', 11, 150;
我想知道有多少乘客飞往 JFK 和 DXB。
预期结果:
SELECT 'STL', 'JFK', 24, 187
UNION ALL
SELECT 'JFK', 'DXB', 24, 171
到目前为止我的尝试:
WITH CTE1 AS
(
SELECT DEP, SUM(First_Class) AS First_Class, SUM(Economy) AS Economy FROM #PAX GROUP BY DEP HAVING COUNT(DEP) > 1
),
LEG2 AS
(
SELECT ARR, SUM(First_Class) AS First_Class, SUM(Economy) AS Economy FROM #PAX GROUP BY ARR HAVING COUNT(ARR) > 1
)
SELECT #PAX.DEP, #PAX.ARR, CTE1.DEP, LEG2.ARR,
SUM(CTE1.First_Class) AS First_Class, SUM(CTE1.Economy) AS Economy
,SUM(LEG2.First_Class) AS First_Class, SUM(LEG2.Economy) AS Economy
FROM #PAX LEFT OUTER JOIN CTE1 ON #PAX.DEP = CTE1.DEP
LEFT OUTER JOIN LEG2 ON #PAX.ARR = LEG2.ARR
GROUP BY #PAX.DEP, #PAX.ARR
, CTE1.DEP, LEG2.ARR
解决方案
根据您提供的数据,您可以使用 GROUP BY 查询根据 ARR 列进行分组:
SELECT ARR, SUM(First_Class) AS 'TOTAL_FIRST_CLASS',
SUM(Economy) AS 'TOTAL_ECONOMY',
SUM(First_Class) + SUM(Economy) AS 'TOTAL_PASSENGER'
FROM #PAX
GROUP BY ARR
根据评论修改答案:
SELECT DEP, '' AS ARR, SUM(First_Class) AS 'TOTAL_FIRST_CLASS', SUM(Economy) AS 'TOTAL_ECONOMY', SUM(First_Class) + SUM(Economy) AS 'TOTAL_PASSENGER'
FROM #PAX
WHERE DEP NOT IN (SELECT DISTINCT(ARR) FROM #PAX)
GROUP BY DEP
UNION ALL
SELECT '' AS DEP, ARR, SUM(First_Class) AS 'TOTAL_FIRST_CLASS', SUM(Economy) AS 'TOTAL_ECONOMY', SUM(First_Class) + SUM(Economy) AS 'TOTAL_PASSENGER'
FROM #PAX
WHERE ARR NOT IN (SELECT DISTINCT(DEP) FROM #PAX)
GROUP BY ARR
在提供规则以确定特定的 DEP 和 ARR 列值之前,无法根据提供的样本数据和预期结果确定相同的值。
更新的答案(基于提供的数据):
SELECT CASE
WHEN DEPCOL = '' THEN (SELECT DISTINCT(ARR) FROM #PAX WHERE ARR != ARRCOL )
ELSE DEPCOL
END as 'CALC_DEP',
CASE
WHEN ARRCOL = '' THEN (SELECT DISTINCT(DEP) FROM #PAX WHERE DEP != DEPCOL )
ELSE ARRCOL
END as 'CALC_ARR',
TOTAL_FIRST_CLASS,
TOTAL_ECONOMY
FROM
(
SELECT DEP as DEPCOL, '' AS ARRCOL, SUM(First_Class) AS 'TOTAL_FIRST_CLASS', SUM(Economy) AS 'TOTAL_ECONOMY', SUM(First_Class) + SUM(Economy) AS 'TOTAL_PASSENGER'
FROM #PAX
WHERE DEP NOT IN (SELECT DISTINCT(ARR) FROM #PAX)
GROUP BY DEP
UNION ALL
SELECT '' AS DEPCOL, ARR AS ARRCOL, SUM(First_Class) AS 'TOTAL_FIRST_CLASS', SUM(Economy) AS 'TOTAL_ECONOMY', SUM(First_Class) + SUM(Economy) AS 'TOTAL_PASSENGER'
FROM #PAX
WHERE ARR NOT IN (SELECT DISTINCT(DEP) FROM #PAX)
GROUP BY ARR
) AS tflight
推荐阅读
- java - VS Code 在本地存储库中看不到更新的依赖项
- webpack - 当 Sass 写入 .css 文件时,它会破坏 webpack。有解决方法吗?
- calculated-field - Tableau 中的计算字段
- php - 设置 PHPunit - PHP CLI 不执行 shell 脚本
- java - Java swing 应用程序导致 X 服务器中的像素图泄漏
- node.js - 如何使用猫鼬填充嵌套数组
- kibana - Kibana 设置遥测:通过 API 启用为 false
- python - 如何从第二个数据框中获取新记录?
- java - 带有报告生成器的 HtmlUnit:生成后获取报告正文
- ios - 将本机屏幕渲染反应为空白但仍可点击