选择每个 GROUP BY 组中的第一行？

admin

选择每个 GROUP BY 组中的第一行？

sql

正如标题所暗示的那样，我想选择每组以GROUP BY.

具体来说，如果我有一个purchases看起来像这样的表：

SELECT * FROM purchases;

我的输出：

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

我想查询的id最大购买（的total）每个制造customer。像这样的东西：

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

预期输出：

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

阅读 175

2021-06-07

共1个答案

admin

在支持 CTE 和窗口函数的数据库上：

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

任何数据库都支持：
但是你需要添加逻辑来打破关系：

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

2021-06-07