通过SUM排序Oracle查询,而无需选择SUM [英] Order Oracle query by SUM without selecting the SUM
本文介绍了通过SUM排序Oracle查询,而无需选择SUM的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张类似于以下表格的表格:
I have a table somewhat like the following one:
lot | defect | quantity
-------+----------+-----------
lot1 | c | 7
lot1 | c | 2
lot3 | e | 5
lot3 | b | 9
lot3 | a | 5
lot2 | d | 4
lot4 | c | 12
... | ... | ...
我想对两行之间的批次和缺陷相等的数量求和,然后按其数量的总和排序(lot3 = 9 + 5 + 5 = 19,lot4 = 12 ,lot1 = 7 + 2 = 9,lot2 = 4),然后是数量(在每手内部),然后是缺陷.
I want to sum the quantities where the lot and defect are equal between rows and then order the lot by the sum of its quantity (lot3=9+5+5=19, lot4=12, lot1=7+2=9, lot2=4), then the quantity (inside each lot), and then the defect.
因此它应该导致以下结果:
So it should result in the following:
lot | defect | SUM(quantity)
-------+----------+----------------
lot3 | b | 9
lot3 | a | 5
lot3 | e | 5
lot4 | c | 12
lot1 | c | 9
lot2 | d | 4
... | ... | ...
我能想到的最接近的是以下查询:
The closest I can think of is the following query:
SELECT lot, defect, SUM(quantity)
FROM table
GROUP BY lot, defect
ORDER BY SUM(quantity), lot, defect
结果如下:
lot | defect | SUM(quantity)
-------+----------+----------------
lot4 | c | 12
lot1 | c | 9
lot3 | b | 9
lot3 | a | 5
lot3 | e | 5
lot2 | d | 4
... | ... | ...
推荐答案
您的问题似乎与排序结果有关.解决方案是在ORDER BY
中使用窗口函数:
Your question seems to be about ordering the results. The solution is to use window functions in ORDER BY
:
SELECT lot, defect, SUM(quantity)
FROM table
GROUP BY lot, defect
ORDER BY SUM(SUM(quantity)) OVER (PARTITION BY lot) DESC,
lot, SUM(quantity) DESC, defect;
这篇关于通过SUM排序Oracle查询,而无需选择SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文