如何在GROUP BY与FOR XML PATH组合中按POSTING DATE倒序排序供应商

现有如下SQL代码创建临时表#AD并插入数据，当前通过FOR XML PATH拼接同一PART NUMBER和PLANT下的供应商信息，但需要将供应商按POSTING DATE倒序排列（最近的供应商优先，示例中VEND2在前）。尝试在FOR XML PATH前添加ORDER BY [POSTING DATE] DESC时，因GROUP BY的限制无法实现；尝试子查询排序也失败，求可行解决方案。

原代码：

CREATE TABLE #AD 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
)

INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
       ('123', '1001', '2', 'VEND2', 20220611)

SELECT 
    AD.*,
    REPLACE(STUFF((SELECT (', (' + AD2.[VENDOR CODE] + ') ' + AD2.[SUPPLIER NAME])
                   FROM #AD AD2
                   WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
                     AND AD.PLANT = AD2.PLANT 
                     AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
                   GROUP BY AD2.[VENDOR CODE], AD2.[SUPPLIER NAME] 
                   FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM  
    #AD AD

解决方案 #

核心思路是先获取每个供应商（按VENDOR CODE和SUPPLIER NAME分组）对应的最新POSTING DATE，再基于这个日期排序后执行拼接，避开GROUP BY无法直接引用未分组字段排序的问题。

方法1：用HAVING子句获取最新日期 #

CREATE TABLE #AD 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
)

INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
       ('123', '1001', '2', 'VEND2', 20220611)

SELECT 
    AD.*,
    REPLACE(STUFF((SELECT ', (' + AD2.[VENDOR CODE] + ') ' + AD2.[SUPPLIER NAME]
                   FROM (
                       -- 先筛选出每个供应商的最新POSTING DATE记录
                       SELECT [VENDOR CODE], [SUPPLIER NAME], [POSTING DATE]
                       FROM #AD
                       WHERE [PART NUMBER] = AD.[PART NUMBER] 
                         AND [PLANT] = AD.[PLANT]
                         AND [POSTING DATE] BETWEEN 20220101 AND 20221231
                       GROUP BY [VENDOR CODE], [SUPPLIER NAME], [POSTING DATE]
                       HAVING [POSTING DATE] = MAX([POSTING DATE])
                   ) AD2
                   ORDER BY AD2.[POSTING DATE] DESC -- 此处可正常按日期降序排序
                   FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM #AD AD

方法2：用窗口函数标记最新记录（更简洁） #

CREATE TABLE #AD 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
)

INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
       ('123', '1001', '2', 'VEND2', 20220611)

WITH SupplierLatest AS (
    SELECT 
        [PART NUMBER], [PLANT], [VENDOR CODE], [SUPPLIER NAME], [POSTING DATE],
        -- 按PART+PLANT+供应商信息分组，给记录按POSTING DATE降序排名
        ROW_NUMBER() OVER (PARTITION BY [PART NUMBER], [PLANT], [VENDOR CODE], [SUPPLIER NAME] ORDER BY [POSTING DATE] DESC) AS rn
    FROM #AD
    WHERE [POSTING DATE] BETWEEN 20220101 AND 20221231
)
SELECT 
    AD.*,
    REPLACE(STUFF((SELECT ', (' + SL.[VENDOR CODE] + ') ' + SL.[SUPPLIER NAME]
                   FROM SupplierLatest SL
                   WHERE SL.[PART NUMBER] = AD.[PART NUMBER] 
                     AND SL.[PLANT] = AD.[PLANT]
                     AND SL.rn = 1 -- 只取每个供应商的最新记录
                   ORDER BY SL.[POSTING DATE] DESC
                   FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM #AD AD

方案说明 #

原问题的核心矛盾是：GROUP BY子句仅包含VENDOR CODE和SUPPLIER NAME，无法直接用未在GROUP BY或聚合函数中的POSTING DATE排序。通过先获取每个供应商对应的最新POSTING DATE记录（无论是HAVING子句筛选还是窗口函数标记），就能在子查询中基于该日期正常排序，实现按最近日期优先拼接供应商的需求。

期望输出 #

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