SQL 从组中按最大值选择行

Question

我正在尝试选择具有不同其他列选择的组中的最大值(计算(

根据下面的表格数据，我想选择具有最高数量(数量计划(和明显选择 Len 和 Wid 的行

表数据如下

+-----------+-----------+---------+---------+------------+---------+
|   Ident   |   Name    | Len     |   Wid   |      Qty   |    Plan |
+-----------+-----------+---------+---------+------------+---------+
|  12345    | Name1     | 1500    |    1000 |         20 |       5 |
|  23456    | Name1     | 1500    |    1000 |         30 |      13 |
|  34567    | Name1     | 2500    |    1000 |         10 |       2 |
|  45678    | Name1     | 2500    |    1000 |         10 |       4 |
|  56789    | Name1     | 1500    |    1200 |         20 |       3 |
|  00001    | Name2     | 1500    |    1200 |         10 |       6 |
|  00002    | Name2     | 1500    |    1200 |         20 |       7 |
|  00003    | Name3     | 1500    |    1200 |         30 |       5 |
|  00004    | Name3     | 1500    |    1200 |         40 |       4 |
+-----------+-----------+---------+---------+------------+---------+

使用我的查询，我无法删除"较低"值：

select a.Ident ,a.Name, a.Len,a.Wid, a.Qnt-a.Plan as Amount
from table a
join (select ident, max(Qnt - Plan) Amount
      from table
      where Name = 'Name1'
      group by Ident, Len, Wid) b
  on b.Ident = a.Ident and b.Amount = a.Qnt-a.Plan
order by Amount desc

题外话：为什么我不能使用 ->其中 b.Amount = a.金额(他不知道 a.金额(???

我想要的选择应该看起来像：

+-----------+-----------+---------+---------+------------+
|   Ident   |   Name    | Len     |   Wid   |   Amount   |
+-----------+-----------+---------+---------+------------+
|  56789    | Name1     | 1500    |    1200 |         18 |
|  23456    | Name1     | 1500    |    1000 |         17 |
|  34567    | Name1     | 2500    |    1000 |          8 |
+-----------+-----------+---------+---------+------------+

提前非常感谢

Answer 1

目前尚不清楚您使用的是哪种数据库，但此解决方案应该适用于任何数据库：

SELECT tab.Ident,
       tab.Name,
       tab.Len,
       tab.Wid,
       (tab.Qty - tab.Plan) AS Amount
FROM   (SELECT   Name,
                 Len,
                 Wid,
                 MAX(Qty-Plan) AS Amount
        FROM     my_table
        GROUP BY Name,
                 Len,
                 Wid
       ) AS grouped
JOIN   my_table tab
  ON   grouped.Name = tab.Name
 AND   grouped.Len = tab.Len
 AND   grouped.Wid = tab.Wid
 AND   grouped.Amount = (tab.Qty - tab.Plan)
 AND   tab.Name = 'Name1'

Answer 2

另一种方法，使用窗口函数来简化事情：

SELECT ident, name, len, wid, qnt - [plan] AS amount
FROM  (SELECT *, row_number() OVER (PARTITION BY len, wid ORDER BY qnt - [plan] DESC) AS rn
       FROM test WHERE name = 'Name1') AS sq
WHERE rn = 1
ORDER BY amount DESC;

SQL 小提琴示例。