每个门都有3种可能的延迟类别。我试图在每个类别的某些门处找到Tot_Delay_Min，因此，我需要找到一种方法来将这些“延迟类别”组合为一个列，并将该类别的总分钟数合并为另一列。DLY1MIN，DLY2MIN和DLY3MIN对应于DLY1CAT，DLY2CAT和DLY3CAT。
这是我为一个门准备的样本：
Gate DLY1CAT DLY1MIN DLY2CAT DLY2MIN DLY3CAT DLY3MIN
==== ======= ======= ======= ======= ======= =======
SDF Weather 50 Fuel 3
SDF Late Jet 10 Weather 9
SDF Deice 9
SDF Late Jet 7 Fuel 11
SDF Computer 20 Weather 13
SDF Load Issue 8 Deice 5 Fuel 3
SDF Damage 20 Deice 7
这是我想要的示例：
Gate DLYCAT DLYMIN
==== ======= =======
SDF Weather 72
SDF Deice 21
SDF Computer 20
SDF Damage 20
SDF Fuel 17
SDF Late Jet 17
SDF Load Issue 8
有关如何进行此操作的任何提示？
这未经测试，我敢肯定有更好的方法可以做到这一点，但这应该可行：
SELECT Gate, Category, SUM("Delay Minutes") AS "Delay Minutes"
FROM
(
SELECT Gate AS Gate,
Dly1Cat AS Category,
DLY1Min AS "Delay Minutes"
FROM YourTable
UNION ALL
SELECT Gate AS Gate,
Dly2Cat AS Category,
DLY2MIN AS "Delay Minutes"
FROM YourTable
UNION ALL
SELECT Gate AS Gate,
Dly3Cat AS Category,
DLY3MIN AS "Delay Minutes"
FROM YourTable
)
WHERE Category IS NOT NULL
GROUP BY Gate, Category;
您在这里所做的是针对每个类别进行三个单独的查询-然后通过UNION ALL将它们组合在一起。然后，从合并的结果集中选择并按Gate和新创建的“ Category”列进行分组。
编辑：
删除了三个内部查询中的分组，因为它们是不必要的。
第二编辑：
由于是Oracle，所以将方括号更改为刻度线-谢谢，a_horse_with_no_name
第三编辑：
仅在需要时包括引号。在末尾添加了分号。删除表别名以实现Oracle兼容性。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句