如何编写无重复代码的T-SQL查询？解决同过滤表多场景复用问题

针对你提到的两个重复问题，可通过以下T-SQL方案解决：

1. 预过滤table1，避免重复扫描 #

使用CTE（公共表表达式）预先筛选出table1中符合@TableID条件的记录，后续所有关联或转换操作都基于这个预过滤结果，确保table1只被扫描过滤一次，避免重复计算。

2. 合并相似UNION ALL逻辑 #

将UNPIVOT转换为CROSS APPLY + VALUES的写法，这种方式比传统UNPIVOT更灵活，且能和后续两个关联查询的UNION ALL结构更统一，减少冗余代码。

优化后的完整函数 #

CREATE FUNCTION unpivoter(@TableID int)
RETURNS TABLE
AS
RETURN
(
    WITH filtered_t1 AS (
        -- 仅过滤一次table1，后续所有操作复用此结果
        SELECT id, foo1, foo2, foo3, foo4
        FROM table1
        WHERE id = @TableID
    )
    -- 替代UNPIVOT的部分：将列转为行
    SELECT ca.a, ca.b
    FROM filtered_t1
    CROSS APPLY (
        VALUES 
            (foo1, 'foo1'),
            (foo2, 'foo2'),
            (foo3, 'foo3'),
            (foo4, 'foo4')
    ) ca(a, b)

    UNION ALL

    -- 关联t2、t3的部分
    SELECT t3.a, t3.b
    FROM filtered_t1 t1
    JOIN t2 ON t1.id = t2.id
    JOIN t3 ON t3.id = t2.id

    UNION ALL

    -- 关联t4的部分（修正原代码中错误的JOIN条件）
    SELECT t4.a, t4.b
    FROM filtered_t1 t1
    JOIN t4 ON t4.id = t1.id
)

额外说明 #

• 原代码中第三个UNION ALL的JOIN条件t4.id = t4.id是无效的（会返回t4所有行），推测应为t4.id = t1.id，已在优化代码中修正。
• 使用CROSS APPLY + VALUES替代UNPIVOT的优势：不仅能实现列转行，还可灵活添加额外计算逻辑，且在多数场景下性能与UNPIVOT相当甚至更优。
• 预过滤的CTE不会带来性能损耗：SQL Server的查询优化器会将CTE展开为查询计划的一部分，不会额外生成临时表，确保性能最优。

内容的提问来源于stack exchange，提问作者J. Mini