分组时如何选择表的其他列？[副本]

Question

请假设下表：

// mytable
+--------+-------------+---------+
|  num   | business_id | user_id |
+--------+-------------+---------+
| 3      | 503         | 12      |
| 7      | 33          | 12      |
| 1      | 771         | 13      |
| 2      | 86          | 13      |
| 1      | 772         | 13      |
| 4      | 652         | 14      |
| 4      | 567         | 14      |
+--------+-------------+---------+

我需要根据user_id对其进行分组，因此，下面是我的查询：

select max(num), user_id from mytable
group by user_id

结果如下：

// res
+--------+---------+
|  num   | user_id |
+--------+---------+
| 7      | 12      |
| 2      | 13      |
| 4      | 14      |
+--------+---------+

现在我还需要获取这些行的business_id。以下是预期的结果：

// mytable
+--------+-------------+---------+
|  num   | business_id | user_id |
+--------+-------------+---------+
| 7      | 33          | 12      |
| 2      | 86          | 13      |
| 4      | 567         | 14      |  -- This is selected randomly, because of the equality of values
+--------+-------------+---------+

知道我该怎么做吗？

Answer 1

你不组队。你过滤。一种方法使用窗口函数，如row_number():

select t.*
from (select t.*,
             row_number() over (partition by user_id order by num desc) as seqnum
      from mytable t
     ) t
where seqnum = 1;

另一种在(user_id,num)上使用索引性能稍好的方法是相关子查询：

select t.*
from mytable t
where t.num = (select max(t2.num)
               from mytable t2
               where t2.user_id = t.user_id
              );

当您想要汇总行时，应该考虑“分组依据”。当要选择具有特定特征的行时，您应该考虑“where”。