带CTE的T-SQL联合查询排序报错问题求助

错误原因 #

你遇到的语法错误是因为ORDER BY子句不能放在UNION的单个子查询中，必须放在整个联合查询的末尾。另外要让id=9的行固定在首行，需要额外添加一个排序辅助列来控制优先级。

修正后的查询语句 #

WITH s1
AS (select '1' as id
    union
    select '4' as id
    union
    select '2' as id
    union
    select '3' as id
    union
    select '5' as id
   ),
     s2
AS (select '4' as id,
           100 as val
    union
    select '1' as id,
           500 as val
    union
    select '3' as id,
           700 as val
    union
    select '2' as id,
           800 as val
   )
-- 给id=9的行添加排序优先级列sort_col=0，其他行sort_col=1
select id, val, 0 as sort_col from (select '9' as id, 700 as val) t
UNION
select s1.id, s2.val, 1 as sort_col from s1
Left join s2 on s1.id = s2.id
-- 最后统一排序：先按sort_col升序（0优先），再按val升序，NULL自动排在最后
ORDER BY sort_col, val ASC;

关键说明 #

1. 排序辅助列：通过sort_col区分首行和其他行，确保id=9的行始终排在最前面。
2. ORDER BY位置：必须放在整个UNION语句的最后，作用于联合后的完整结果集。
3. NULL处理：T-SQL中使用ORDER BY ... ASC时，NULL值默认会排在所有非NULL值之后，符合你的期望输出。

执行结果 #

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