首先，我来帮你把这段Oracle查询完整转换成SQL Server的写法。先拆解一下原查询的核心逻辑：

1. 从trans_his和fund关联后，按contact_id、created_dt、mycase分组汇总my_gross_amount
2. 取上述分组结果的全局前3条（通过rownum < 4）
3. 按contact_id和created_dt分组，将mycase用逗号拼接成字符串
4. 最终从trans_his表输出这个拼接后的DetailNotes

严格匹配原Oracle逻辑的SQL Server查询 #

SQL Server里没有LISTAGG函数，我们用STUFF + FOR XML PATH('')来实现字符串拼接，同时用TOP 3替代Oracle的rownum < 4：

SELECT
    (SELECT STUFF((
        SELECT ', ' + a.mycase
        FROM (
            -- 替换Oracle的rownum <4，取全局前3条分组结果
            SELECT TOP 3
                grouped_data.contact_id, 
                grouped_data.created_dt, 
                grouped_data.my_gross_amount, 
                grouped_data.mycase
            FROM (
                -- 原Oracle的分组逻辑
                SELECT 
                    th.CONTACT_ID, 
                    th.created_dt, 
                    SUM(th.my_gross_amount) AS my_gross_amount, 
                    th.mycase
                FROM mySch.trans_his th
                JOIN mySch.fund f ON f.fund = th.fund
                GROUP BY th.contact_id, th.created_dt, th.mycase
            ) AS grouped_data
            ORDER BY grouped_data.my_gross_amount -- 对应LISTAGG里的排序规则
        ) AS a
        GROUP BY a.contact_id, a.created_dt
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS DetailNotes
FROM mySch.trans_his t

修正为「分组内取前3条」的常见场景 #

不过这里要提醒一下：原Oracle查询里的rownum <4是取全局前3条分组结果，这大概率不是你真实想要的逻辑。通常这类需求是要每个contact_id + created_dt组内取前3个mycase（按my_gross_amount排序）再拼接。如果是这个需求，我们需要用ROW_NUMBER()来实现分组内的行号筛选：

SELECT
    (SELECT STUFF((
        SELECT ', ' + ranked_data.mycase
        FROM (
            SELECT 
                th.CONTACT_ID, 
                th.created_dt, 
                SUM(th.my_gross_amount) AS my_gross_amount, 
                th.mycase,
                -- 按contact_id+created_dt分区，按my_gross_amount排序，生成组内行号
                ROW_NUMBER() OVER (PARTITION BY th.contact_id, th.created_dt ORDER BY SUM(th.my_gross_amount)) AS rn
            FROM mySch.trans_his th
            JOIN mySch.fund f ON f.fund = th.fund
            GROUP BY th.contact_id, th.created_dt, th.mycase
        ) AS ranked_data
        -- 筛选组内前3条
        WHERE ranked_data.rn <= 3
          AND ranked_data.CONTACT_ID = t.CONTACT_ID
          AND ranked_data.created_dt = t.created_dt
        ORDER BY ranked_data.my_gross_amount
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS DetailNotes
FROM mySch.trans_his t

关键转换点说明 #

• LISTAGG替代：SQL Server用STUFF((SELECT ', ' + 字段 FROM ... FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')来实现字符串拼接，TYPE和.value()是为了避免特殊字符被转义
• rownum替代：全局取前N条用TOP N；分组内取前N条用ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)
• 排序保持：原OracleLISTAGG里的ORDER BY my_gross_amount，在SQL Server里要放到子查询的ORDER BY中，保证拼接顺序一致

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