sql - 如何在Sql Server 中获取最大值的多个字段？

Question

我有一张盒子表。这些字段是 CartonGroup(这个盒子也属于哪个公司)、盒子的大小(可以用作名称)和尺寸。这是该表的样子:

+--------------+--------+-----------+-----------+-----------+------------+
| CARTON_GROUP |  Size  |  HEIGHT   |   WIDTH   |  LENGTH   | MAX_WEIGHT |
+--------------+--------+-----------+-----------+-----------+------------+
|        09001 | 900129 | 28.000000 | 20.000000 | 20.000000 | 40.000000  |
|        09001 | 900128 | 26.000000 | 20.000000 | 18.000000 | 40.000000  |
|        09001 | 900127 | 26.000000 | 20.000000 | 16.000000 | 40.000000  |
|        09001 | 900126 | 26.000000 | 20.000000 | 14.000000 | 40.000000  |
|        09001 | 900125 | 24.000000 | 18.000000 | 16.000000 | 40.000000  |
|        09001 |  90019 | 20.000000 | 16.500000 | 4.500000  | 40.000000  |
|        09001 |  90018 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        09001 |  90017 | 23.000000 | 18.500000 | 3.000000  | 40.000000  |
|        09001 |  90016 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        09001 |  90015 | 10.000000 | 8.000000  | 6.000000  | 40.000000  |
|        09001 |  90014 | 15.000000 | 12.000000 | 2.000000  | 40.000000  |
|        09001 |  90013 | 15.000000 | 12.000000 | 1.000000  | 40.000000  |
|        09001 |  90012 | 9.500000  | 0.250000  | 12.500000 | 40.000000  |
|        09001 |  90011 | 10.000000 | 0.250000  | 10.000000 | 40.000000  |
|        06003 |  60039 | 26.000000 | 18.000000 | 20.000000 | 40.000000  |
|        06003 |  60038 | 26.000000 | 12.000000 | 20.000000 | 40.000000  |
|        06003 |  60037 | 26.000000 | 10.000000 | 20.000000 | 40.000000  |
|        06003 |  60036 | 26.000000 | 8.000000  | 20.000000 | 40.000000  |
|        06003 |  60035 | 26.000000 | 6.000000  | 20.000000 | 40.000000  |
|        06003 |  60034 | 18.000000 | 10.000000 | 14.000000 | 40.000000  |
|        06003 |  60033 | 16.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06003 |  60032 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06003 |  60031 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        06001 |  60013 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06001 |  60012 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        06001 |  60011 | 12.000000 | 4.000000  | 10.000000 | 40.000000  |
|        04001 |  40013 | 15.000000 | 25.000000 | 20.000000 | 70.000000  |
|        04001 |  40012 | 18.000000 | 18.000000 | 18.000000 | 40.000000  |
|        04001 |  40011 | 8.000000  | 5.000000  | 6.000000  | 40.000000  |
|        03008 |  30086 | 16.000000 | 8.000000  | 12.000000 | 40.000000  |
|        03008 |  30085 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        03008 |  30084 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
+--------------+--------+-----------+-----------+-----------+------------+

我想做的是为每个纸箱组找到最大的盒子。这很简单，这就是你如何做到的

select
CARTON_GROUP
,MAX(HEIGHT*WIDTH*LENGTH) Cube
from CARTONS
group by CARTON_GROUP

但我还想在该计算中包括大小(名称)。但我不能那样做，因为我不能将大小包含在分组依据中，因为它是唯一的，因此无法正确分组。

而且我不能对这样的大小执行聚合函数

,max(Size) size

因为我不想要分组依据的最大尺寸，所以我想要分组依据的最大框的尺寸(尺寸的最大值)。

而且我不能只将该表与立方体连接回原始表，因为如果两个不同的盒子具有相同的立方体计算结果会怎样。

有什么办法吗？

Answer 1

使用row_number():

select c.*, (height*width*length) as volume
from (select c.*,
             row_number() over (partition by carton_group order by height*width*length desc) as seqnum
      from CARTONS c
     ) c
where seqnum = 1;

在平局的情况下，这将返回一个任意纸箱，最大。如果你想在有联系时使用所有这些，请使用 rank()。

如果您对维度有限制，请将其放在子查询中:

select c.*, (height*width*length) as volume
from (select c.*,
             row_number() over (partition by carton_group order by height*width*length desc) as seqnum
      from CARTONS c
      where max_weight <= 40
     ) c
where seqnum = 1;