按 SUM 排序 Oracle 查询而不选择 SUM [英] Order Oracle query by SUM without selecting the 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屋！