SQL Server 2008拼接临时表去重列值为varchar时报ORDER BY错误如何解决

错误原因 #

除非同时指定了TOP或FOR XML，否则ORDER BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效

该报错触发的核心原因是SQL Server 2008的语法校验限制：在带DISTINCT的FOR XML拼接子查询中混用ORDER BY时，校验逻辑会判定该场景下的ORDER BY不符合规范，即使子查询已经带了FOR XML修饰。

解决方案 #

调整子查询结构，先完成去重逻辑，再在外层执行排序和XML拼接操作，即可避开语法校验冲突，修正后的代码示例如下：

(SELECT
        STUFF((
        SELECT ', ' + CountryName -- 分隔符可按需修改，长度和STUFF删除长度匹配即可
        FROM (
            -- 内层先单独完成去重逻辑，不需要加ORDER BY
            SELECT DISTINCT #SomeTemporaryTable.CountryName
            FROM #SomeTemporaryTable
            WHERE (#SomeTemporaryTable.SomeId = 1
            AND #SomeTemporaryTable.AnotherId = 2)
        ) AS DistinctCountry
        -- 排序逻辑移到外层，配合FOR XML符合语法要求，不需要排序可直接删除该行
        ORDER BY CountryName
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
    ,1,2,'') AS BreakDownLocationCountry

适配说明 #

• 如果不需要对拼接的国家名排序，直接删除ORDER BY CountryName行即可，不会触发报错
• STUFF的第二个、第三个参数要和你使用的分隔符长度匹配：如果用单字符分隔符（例如|），将参数调整为1,1,''即可
• 该写法完全兼容SQL Server 2008版本，不需要修改数据库兼容等级

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