SQL Server多表GROUP BY查询返回3行 需仅输出1行问题求助

问题根因 #

你当前的分组逻辑包含了o.cFName、o.cLName两个字段，只要这两个字段值不同，就会被拆分为独立分组，自然会返回多行重复的Id、DateTime记录。

解决方案 #

第一步：调整分组逻辑，合并同组的Ref值 #

不同数据库的字符串聚合函数有差异，以下是主流数据库的写法：

• SQL Server 2017+/Azure SQL：使用STRING_AGG函数，指定换行符作为分隔符

Select 
    a.Id, 
    a.DateTime, 
    STRING_AGG(o.cFName + ' ' + o.cLName, CHAR(13)) as Ref 
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
    Official o On o.Id = g.OfficialId
Where 
    Convert(date, a.DateTime) = '09/04/2021' 
    And f.Id = 3
Group by 
    a.Id, a.DateTime 
Order by 
    a.DateTime

• MySQL：使用GROUP_CONCAT函数，指定换行符作为分隔符

Select 
    a.Id, 
    a.DateTime, 
    GROUP_CONCAT(o.cFName, ' ', o.cLName SEPARATOR '\n') as Ref 
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
    Official o On o.Id = g.OfficialId
Where 
    Date(a.DateTime) = '2021-09-04'
    And f.Id = 3
Group by 
    a.Id, a.DateTime 
Order by 
    a.DateTime

• Oracle：使用LISTAGG函数

Select 
    a.Id, 
    a.DateTime, 
    LISTAGG(o.cFName || ' ' || o.cLName, CHR(10)) WITHIN GROUP (ORDER BY o.cFName) as Ref 
From 
    GameAssignment g 
Left Outer Join 
    games a on g.gameId = a.Id 
Left Outer Join 
    FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
    Official o On o.Id = g.OfficialId
Where 
    Trunc(a.DateTime) = DATE '2021-09-04'
    And f.Id = 3
Group by 
    a.Id, a.DateTime 
Order by 
    a.DateTime

第二步：展示适配 #

如果需要实现Id、DateTime只在第一行显示，后续行对应位置留空的效果，属于前端展示层的逻辑：

• 可以直接在前端遍历结果集时，判断当前行的Id、DateTime和上一行是否相同，相同则渲染为空值即可
• 如果必须在SQL层面实现，可以用窗口函数ROW_NUMBER()标记组内行序，非首行的Id、DateTime返回空值：

-- 以SQL Server为例
WITH grouped_data AS (
    Select 
        a.Id, 
        a.DateTime, 
        o.cFName + ' ' + o.cLName as Ref,
        ROW_NUMBER() OVER(PARTITION BY a.Id, a.DateTime ORDER BY o.cFName) as rn
    From 
        GameAssignment g 
    Left Outer Join 
        games a on g.gameId = a.Id 
    Left Outer Join 
        FieldGym f On a.FieldGym = f.Id 
    Left Outer Join 
        Official o On o.Id = g.OfficialId
    Where 
        Convert(date, a.DateTime) = '09/04/2021' 
        And f.Id = 3
)
SELECT 
    CASE WHEN rn = 1 THEN Id ELSE NULL END AS Id,
    CASE WHEN rn = 1 THEN DateTime ELSE NULL END AS DateTime,
    Ref
FROM grouped_data
ORDER BY Id, DateTime, rn

内容的提问来源于stack exchange，提问作者mlg74