时间戳

首页 > 解决方案 > 对分组依据和聚合结果的 SQL 查询

sql-server - 对分组依据和聚合结果的 SQL 查询

问题描述

鉴于此示例数据：

select 'Ada' name, 'Apple' fruit, 2 score union all
select 'Ada', 'Apple', 10 union all
select 'Ada', 'Banana', 8 union all
select 'Ada', 'Cherry', 8 union all
select 'Bob', 'Apple', 5

我想提出一个查询，将数据汇总为

name | fruit         | score
-----+---------------+------
 Ada | Apple         | 10
 Ada | Banana,Cherry | 8
 Bob | Apple         | 5

即，如果它们具有相同的分数，则按名称和水果组合，如果它们具有相同的水果，则按最高分进行分组

我可以通过

select name, string_agg(fruit, ','), score from (
    select name, fruit, max(score) score from (
        select 'Ada' name, 'Apple' fruit, 2 score union all
        select 'Ada', 'Apple', 10 union all
        select 'Ada', 'Banana', 8 union all
        select 'Ada', 'Cherry', 8 union all
        select 'Bob', 'Apple', 5
    ) data
    group by name, fruit
) data
group by name, score

有没有一种更简单的方法可以在一次选择而不是两次选择中编写查询？

如果你能解释为什么不能单选写，“否”是一个可以接受的答案。

标签： sql-servertsql

解决方案

据我所知，这不能一步完成，因为您必须使用两个不同的分组 - 一个获得每个名称和水果的最高分，一个用于string_agg，它基于来自的聚合第一个查询。

话虽如此，它可以使用通用表表达式以更具可读性的方式编写：

WITH cte AS
(
    SELECT name, fruit, max(score) As score 
    FROM (
        SELECT 'Ada' name, 'Apple' fruit, 2 score UNION ALL
        SELECT 'Ada' name, 'Apple' fruit, 2 score UNION ALL
        SELECT 'Ada', 'Apple', 10 UNION ALL
        SELECT 'Ada', 'Banana', 8 UNION ALL
        SELECT 'Ada', 'Cherry', 8 UNION ALL
        SELECT 'Bob', 'Apple', 5
    ) data
    GROUP BY name, fruit
)

SELECT name, STRING_AGG(fruit, ','), score 
FROM cte
GROUP BY name, score

推荐阅读