在使用GROUPBY的ForXMLPath之前排序？

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

期望的輸出：

我想訂購供應商列以首先擁有最近的供應商（最近的發布日期，因此在這種情況下應首先列出 Vend2）。我嘗試只添加一個ORDER BY [POSTING DATE] DESCright before FOR XML PATH，但我不能這樣做，因為我不想將它添加到 select 陳述句或 group by。

我還嘗試做一個子查詢，只選擇按發布日期排序的所有內容，但您不能在子查詢中排序。

有什么想法可以做到這一點嗎？

uj5u.com熱心網友回復：

您仍然可以訂購日期，只是您需要一個聚合函式。

我用ORDER BY MAX(AD2.[POSTING DATE]) DESC那個應該帶來正確的順序

正如我在評論中發布的那樣，更新最近的內容會帶來一些好處，包括STRING_AGG

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] 
  ORDER BY MAX(AD2.[POSTING DATE]) DESC
                   FOR XML PATH('') ), 1, 2, '')
  , '&', '&') AS [Supplier]
FROM  
    #AD AD

小提琴

uj5u.com熱心網友回復：

下面的解決方案怎么樣？

SQL

DECLARE @tbl TABLE 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
);
INSERT INTO @tbl VALUES 
('123', '1001', '1', 'VEND1', 20220304),
('123', '1001', '2', 'VEND2', 20220611);

SELECT * FROM @tbl;

SELECT AD.[PART NUMBER], ad.PLANT
    , REPLACE(STUFF((SELECT (', ('   AD2.[VENDOR CODE]   ') '   AD2.[SUPPLIER NAME])
                   FROM @tbl 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?
                   ORDER BY AD2.[POSTING DATE] DESC
                   FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM @tbl AD
GROUP BY AD.[PART NUMBER], ad.PLANT;

輸出

標籤：sql sql服务器

上一篇：用于多組行的NOTIN多個值的SQLSelect陳述句

下一篇：多用戶更新