Select first row in each GROUP BY group?(在每个 GROUP BY 组中选择第一行?)
问题描述
正如标题所示,我想选择用 GROUP BY
分组的每组行的第一行.
As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY
.
具体来说,如果我有一个如下所示的 purchases
表:
Specifically, if I've got a purchases
table that looks like this:
SELECT * FROM purchases;
我的输出:
身份证 | 客户 | 总计 |
---|---|---|
1 | 乔 | 5 |
2 | 莎莉 | 3 |
3 | 乔 | 2 |
4 | 莎莉 | 1 |
我想查询每个客户
的最大购买量(total
)的id
.像这样的:
I'd like to query for the id
of the largest purchase (total
) made by each customer
. Something like this:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
预期输出:
FIRST(id) | 客户 | FIRST(总计) |
---|---|---|
1 | 乔 | 5 |
2 | 莎莉 | 3 |
推荐答案
在 支持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
任何数据库都支持:
但你需要添加逻辑来打破关系:
Supported by any database:
But you need to add logic to break ties:
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
这篇关于在每个 GROUP BY 组中选择第一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!