SQL关联表排序实现nulls last及相关报错解决方案

现有两张业务表，分别为 Person 和 PersonSkill，表结构及测试数据如下：

Person表 #

PersonSkill表 #

实现人员排序逻辑：按人员关联的技能字母顺序升序排序，无关联技能的人员排在最后。

原有编写的SQL语句：

SELECT distinct
  p.*,
  STUFF(
    (SELECT ',' + ps.SKILL
      FROM PersonSkill ps
      WHERE ps.PERSON_ID = p.ID
      ORDER BY ps.SORT
      FOR XML PATH('')
    ), 1, 1, '') sortRule
FROM Person p
ORDER BY IIF(sortRule is null, 1, 0) asc, sortRule asc

遇到的报错如下：

• 在ORDER BY子句的IIF或CASE操作中引用别名sortRule时，提示错误：Invalid column name 'sortRule'.
• 若从SELECT列表中移除STUFF生成的sortRule字段，系统提示使用SELECT DISTINCT时必须包含该字段；
• 若直接将STUFF表达式复制到ORDER BY子句中，又提示错误：ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

报错本质是SQL执行顺序限制：SELECT DISTINCT优先级高于ORDER BY，ORDER BY无法直接引用SELECT层定义的别名，也不能引用未出现在DISTINCT返回列中的计算字段。可以将计算逻辑封装到子查询/CTE中，在外层执行排序即可绕开限制：

方案1：子查询实现（不需要返回sortRule字段的场景） #

SELECT ID, NAME
FROM (
    SELECT DISTINCT
      p.ID,
      p.NAME,
      STUFF(
        (SELECT ',' + ps.SKILL
          FROM PersonSkill ps
          WHERE ps.PERSON_ID = p.ID
          ORDER BY ps.SORT
          FOR XML PATH('')
        ), 1, 1, '') sortRule
    FROM Person p
) t
ORDER BY 
  CASE WHEN sortRule IS NULL THEN 1 ELSE 0 END ASC,
  sortRule ASC

方案2：CTE实现（可读性更强） #

WITH PersonWithSkill AS (
    SELECT DISTINCT
      p.*,
      STUFF(
        (SELECT ',' + ps.SKILL
          FROM PersonSkill ps
          WHERE ps.PERSON_ID = p.ID
          ORDER BY ps.SORT
          FOR XML PATH('')
        ), 1, 1, '') sortRule
    FROM Person p
)
-- 不需要返回sortRule就把*替换为ID、NAME即可
SELECT *
FROM PersonWithSkill
ORDER BY 
  IIF(sortRule IS NULL, 1, 0) ASC,
  sortRule ASC

按测试数据最终排序结果为：

1. Person 2（关联技能为Sing，优先级最高）
2. Person 1（关联技能拼接后为Playful,Sing,Bowl,SQL）
3. Person 3（无关联技能，排在最后）

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