SQL Server 2019：子查询中排序追踪码并拼接字符串问题

TrackingCode表是Employment表的子表，需在返回Employment其他数据的同时，将每个Employment对应的所有TrackingCode以逗号分隔字符串形式返回，且该字符串需按照TrackingCodeOrder表定义的Order数值排序。

相关表结构 #

TrackingCodeOrder表

TrackingCode表

当前代码及问题 #

当前使用的子查询代码：

(
    select top(1) string_agg(tc.TrackingCode, ',') 
    from TrackingCode as tc 
    left join TrackingCodeOrder tco on tco.Name = tc.TrackingCode
    where etc.Employment_id = e.id
    group by tco.Order
    order by tco.Order asc
) as 'TrackingCodeId'

from employee e

执行后出现的问题：

• 部分Employment的追踪码返回不全（如应返回2个只返回1个，应返回9个只返回4个）
• 移除top(1)、group by和order by后，能返回全部追踪码但顺序不符合预期
• 使用top 100%时，子查询会返回多行

需求：在子查询中实现正确排序并返回全部追踪码。

问题根源是group by tco.Order会将同一排序值的TrackingCode聚合为一组，再搭配top(1)只取第一组数据，导致其他组的追踪码丢失。

正确做法是利用STRING_AGG的内置排序功能，先按规则排序再聚合，无需额外分组或top操作：

修改后的完整查询代码：

select 
    e.*,
    (
        select string_agg(tc.TrackingCode, ',') within group (order by tco.[Order] asc)
        from TrackingCode as tc 
        left join TrackingCodeOrder tco on tco.Name = tc.TrackingCode
        where tc.Employment_id = e.id
    ) as TrackingCodeId
from employee e

关键说明 #

1. 排序聚合二合一：使用STRING_AGG(...) WITHIN GROUP (ORDER BY tco.[Order] asc)，直接在聚合过程中指定排序依据，确保最终字符串的顺序符合TrackingCodeOrder的定义。
2. 移除冗余语句：删掉group by tco.Order和top(1)，避免分组导致的数据丢失和仅返回部分结果的问题。
3. 修正笔误：原代码中where etc.Employment_id = e.id是拼写错误，改为tc.Employment_id = e.id，否则会出现对象不存在的错误。

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