如何从UNION查询中移除空行并将结果按升序排序

基础修复版（兼容原有写法逻辑） #

空行问题大概率是数据中存在全空格的空白字符串（非NULL值，原有IS NOT NULL条件无法过滤），同时需要增加自定义排序规则保证「All rooms」排在首位，其余结果按升序排列，修改后语句如下：

SELECT [SELECTION:]
FROM (
    -- 固定返回All rooms选项，去掉原TOP 1 FROM tbl1写法，避免表空时无返回
    SELECT 'All rooms' AS [SELECTION:], 1 AS sort_order
    UNION ALL 
    SELECT DISTINCT Room1 AS [SELECTION:], 2 AS sort_order FROM tbl1 WHERE Room1 IS NOT NULL AND TRIM(Room1) != ''
    UNION ALL 
    SELECT DISTINCT Room2 AS [SELECTION:], 2 AS sort_order FROM tbl1 WHERE Room2 IS NOT NULL AND TRIM(Room2) != ''
    UNION ALL 
    SELECT DISTINCT Room3 AS [SELECTION:], 2 AS sort_order FROM tbl1 WHERE Room3 IS NOT NULL AND TRIM(Room3) != ''
) AS result_set
-- 外层二次过滤兜底避免空行
WHERE [SELECTION:] IS NOT NULL AND TRIM([SELECTION:]) != ''
ORDER BY sort_order ASC, [SELECTION:] ASC

高性能优化版 #

原有写法需要对tbl1执行4次扫描，数据量大时性能较差，可以使用行转列语法仅扫描1次表完成全部逻辑，性能提升明显：

SELECT [SELECTION:]
FROM (
    SELECT 'All rooms' AS [SELECTION:], 1 AS sort_order
    UNION ALL
    SELECT DISTINCT room_value AS [SELECTION:], 2 AS sort_order
    FROM tbl1
    -- 把三列Room字段转成行数据，一次性读取所有值
    CROSS APPLY (VALUES (Room1), (Room2), (Room3)) AS temp(room_value)
    WHERE temp.room_value IS NOT NULL AND TRIM(temp.room_value) != ''
) AS result_set
ORDER BY sort_order ASC, [SELECTION:] ASC

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