sql - 如何选择每种类型的最大值

Question

我有一个表格，描述了每个托盘中有多少鱼、蔬菜或肉。

 tray  | qty | type
-------+-----+-------
     1 |   5 | FISH
     2 |   6 | VEG
     2 |   2 | FISH
     2 |   5 | MEAT
     3 |   8 | VEG
     3 |   3 | FISH
     3 |   9 | MEAT
     4 |  10 | MEAT

我们称此表为 R(它是我在查询中创建的子表)。

我想要的是一个表格，上面写着哪种托盘的每种类型的数量最多，如下所示:

  type | tray
-------+------
  FISH |  1 
   VEG |  3
  MEAT |  4

我尝试编写以下查询

SELECT type AS type1, tray
FROM (SELECT ... bla bla) AS R
WHERE R.qty IN (SELECT MAX(qty) FROM R WHERE type = type1);

错误是R不存在，我该如何解决？

Answer 1

我会使用窗口函数来解决这个问题:

select r.type, r.tray
from (select r.*,
             row_number() over (partition by type order by qty desc) as seqnum
      from r
     ) r
where seqnum = 1;