正如标题所示,我想选择用GROUP BY分组的每组行中的第一行。
具体来说,如果我有一个如下所示的采购表:
SELECT * FROM purchases;
我的输出:
id |
customer |
total |
1 |
Joe |
5 |
2 |
Sally |
3 |
3 |
Joe |
2 |
4 |
Sally |
1 |
我想查询每个客户的最大购买量(总购买量)。类似于:
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 |
在Postgres中,您可以这样使用array_agg:
SELECT customer,
(array_agg(id ORDER BY total DESC))[1],
max(total)
FROM purchases
GROUP BY customer
这将为您提供每个客户最大购买量的id。
需要注意的一些事项:
array_agg是一个聚合函数,因此它与GROUP BY一起工作。arrayagg允许您指定仅限于自身的排序,因此它不会约束整个查询的结构。如果您需要执行与默认值不同的操作,还可以使用语法对NULL进行排序。一旦我们构建了数组,我们就使用第一个元素。(Postgres数组是1索引的,而不是0索引的)。您可以以类似的方式对第三个输出列使用array_agg,但max(total)更简单。与DISTINCT ON不同,使用array_agg可以保留GROUP BY,以防出于其他原因需要。
对于SQl Server,最有效的方法是:
with
ids as ( --condition for split table into groups
select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)
)
,src as (
select * from yourTable where <condition> --use this as filter for other conditions
)
,joined as (
select tops.* from ids
cross apply --it`s like for each rows
(
select top(1) *
from src
where CommodityId = ids.i
) as tops
)
select * from joined
不要忘记为使用过的列创建聚集索引