SQL查询四级编码匹配行时如何将多列结果转为独立行并优化

方案1：直接编写高效匹配查询（推荐，性能远优于多次OUTER APPLY） #

直接通过单表查询完成规则匹配，一次返回所需独立行结果，无需额外转置处理：

WITH target_row AS (
    -- 先获取你要查询的目标行的各级编码
    SELECT 
        g1code t1, 
        g2code t2, 
        g3code t3, 
        g4code t4 
    FROM tmp_tbl 
    WHERE gcode_id = 4 -- 可替换为你的查询条件，比如WHERE g4code = 'NLZWO'
)
SELECT t.*
FROM tmp_tbl t, target_row
WHERE 
    t.g1code = target_row.t1 -- 最上级编码必须匹配
    AND (t.g2code IS NULL OR t.g2code = target_row.t2) -- 当前行g2非空则必须匹配目标g2
    AND (t.g3code IS NULL OR t.g3code = target_row.t3) -- 当前行g3非空则必须匹配目标g3
    AND (t.g4code IS NULL OR t.g4code = target_row.t4) -- 当前行g4非空则必须匹配目标g4
-- 按层级从高到低排序，直接得到8、7、4的顺序
ORDER BY 
    CASE 
        WHEN t.g3code IS NULL THEN 1 -- 只有前两级有值，对应id=8
        WHEN t.g4code IS NULL THEN 2 -- 前三级有值，对应id=7
        ELSE 3 -- 四级都有值，对应id=4
    END

方案2：已有平铺多列结果转独立行 #

如果你已经通过多次OUTER APPLY得到了单行平铺的结果，可以用CROSS APPLY + VALUES完成行转列，过滤无效NULL行：

SELECT 
    v.gcode_id,
    v.g1code,
    v.g2code,
    v.g3code,
    v.g4code
FROM 你的现有平铺结果集
CROSS APPLY (
    VALUES 
        -- 按你平铺后的字段名，依次填入四组匹配行的字段
        (id1, g1_1, g2_1, g3_1, g4_1),
        (id2, g1_2, g2_2, g3_2, g4_2),
        (id3, g1_3, g2_3, g3_3, g4_3),
        (id4, g1_4, g2_4, g3_4, g4_4)
) v(gcode_id, g1code, g2code, g3code, g4code)
-- 过滤无匹配的NULL行
WHERE v.gcode_id IS NOT NULL

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