如何实现SQL中保留rn=1行并聚合同LS_NoSerie的DO_Tiers字段？

解决方法 #

方法1：子查询聚合DO_Tiers

直接在SELECT语句中通过子查询，针对每个LS_NoSerie拼接所有DO_Tiers，同时筛选出rn=1的行：

SELECT 
    id,
    LS_NoSerie,
    (SELECT STRING_AGG(DO_Tiers, ',') FROM my_table_name t2 WHERE t2.LS_NoSerie = t1.LS_NoSerie) AS DO_Tiers,
    rn,
    totalMatch
FROM my_table_name t1
WHERE rn = 1;

方法2：先聚合再关联

先通过CTE按LS_NoSerie聚合得到拼接后的DO_Tiers，再和原表中rn=1的行进行关联：

WITH aggregated_tiers AS (
    SELECT 
        LS_NoSerie,
        STRING_AGG(DO_Tiers, ',') AS aggregated_DO_Tiers
    FROM my_table_name
    GROUP BY LS_NoSerie
)
SELECT 
    t.id,
    t.LS_NoSerie,
    a.aggregated_DO_Tiers AS DO_Tiers,
    t.rn,
    t.totalMatch
FROM my_table_name t
JOIN aggregated_tiers a ON t.LS_NoSerie = a.LS_NoSerie
WHERE t.rn = 1;

原SQL报错原因说明 #

原SQL仅按LS_NoSerie分组，但id、rn、totalMatch这些字段既没有被纳入分组条件，也没有用聚合函数（比如MAX()、MIN()）处理。数据库无法确定这些字段要取分组内的哪一个值，因此会抛出语法错误。上面两种方法都绕开了这个问题：方法1通过子查询单独处理聚合逻辑，方法2先完成聚合再关联原始数据，既能获取rn=1行的原始字段值，又能得到聚合后的DO_Tiers结果。

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