sql - 使用 SQL 从每个类别中选择两个元素

Question

考虑下表

id | fruit | score
---+-------+------
1  | pear  | 9
2  | plum  | 5
3  | plum  | 7
4  | apple | 4
5  | plum  | 2
6  | pear  | 3

我需要一个 SQL（POSTGRES 方言）查询，它选择得分最低的苹果和李子，每个类别两个。结果将是

id | fruit | score
---+-------+------
 4 | apple | 4
 5 | plum  | 2
 2 | plum  | 5

我从

SELECT id, fruit, score 
FROM fruit_score 
WHERE fruit IN ('apple', 'plum') 
ORDER BY score

我怎么吃LIMIT水果？

标签： sqlpostgresql

Answer 1

row_number()如果要保证每行有两行，则可以使用fruit：

select fs.*
from (select fs.*, row_number() over (partition by fruit order by score asc) as seqnum
      from fruit_score fs
      where fruit IN ('apple', 'plum') 
     ) fs
where seqnum <= 2;