本文介绍了选择每个 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;
我的输出:
| id | 客户 | 总计 |
|---|---|---|
| 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) | 客户 | 第一个(总计) |
|---|---|---|
| 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 组中的第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！