正如标题所暗示的,我想选择用 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 |
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
但是你需要添加逻辑来打破关系:
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 组中的第一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!