如何在sql中找到每个组的最小值?

Question

我有两个表:

产品:

Group_id    Product_id   Size
------------------------------
A             123         s
B             342         xs
A             654         m
A             952         L
B             853         s
B             134         m

Size_Code:

Size   Size_code
----------------
xs       1
s        2
m        3
L        4

我想从每个产品组中找到最小尺寸的产品

结果如下:

Group_id  Product_id  Size
----------------------------
A            123        s
B            342        xs

如何在SQL中写这个?谢谢!

Answer 1

假设size_code具有排序，则可以使用row_number():

select p.*
from (select p.*,
row_number() over (partition by p.group_id order by s.size_code asc) as seqnum
from product p join
size_code s
on p.size = s.size
) p
where seqnum = 1;

Answer 2

在SQL中(无假设):

SELECT p.*
FROM Product p
INNER JOIN Size_Code s ON s.Size = p.Size
INNER JOIN (
SELECT 
p.Group_id,
min(s.Size_code) as Size_code
FROM Product p
INNER JOIN Size_Code s ON s.Size = p.Size
GROUP BY
p.Group_id
) x on x.Group_id = p.Group_Id AND x.Size_code = s.Size_code
;

:

• DBFIDDLE该
• DBFIDDLE MySQL