SQL Server多子查询如何复用contract scope避免重复编写筛选条件

重复编写相同IN筛选列表的问题，核心是把筛选逻辑抽离为单独的可引用数据源，仅维护一处定义即可让所有引用位置自动同步，以下是不同场景下的可落地方案：

方案1：CTE预定义范围（单查询场景改造成本最低） #

在查询开头用公用表表达式（CTE）统一存储符合条件的contract ID，后续所有需要筛选的位置直接关联该CTE即可，无需重复编写ID列表或筛选逻辑。
改造后的示例代码（修正了原示例的语法错误）：

-- 仅需在此处维护一次合同范围
WITH ContractScope AS (
    -- 如果是固定ID列表，用VALUES子句直接列出即可
    SELECT id FROM (VALUES
        (1001),(1002),(1003),(1005) -- 替换为实际的contract ID
    ) AS c(id)

    -- 如果范围是从业务表按规则筛选得到，直接替换为对应查询即可，例：
    -- SELECT id FROM dim_contract WHERE is_valid = 1 AND contract_type = 'Service'
)
SELECT 
    u.column1,
    u.column2,
    u.column3,
    Table1.sum_col,
    Table2.sum_col2
FROM table_u u (NOLOCK)
-- 主表筛选直接关联CTE
INNER JOIN ContractScope cs ON u.id = cs.id
LEFT JOIN (
    SELECT b.user_id, SUM(b.column) AS sum_col
    FROM table_b b
    -- 子查询筛选关联CTE，无需重复写范围
    INNER JOIN ContractScope cs ON b.user_id = cs.id
    GROUP BY b.user_id
) Table1 ON u.id = Table1.user_id 
LEFT JOIN (
    SELECT b.user_id, SUM(b.column) AS sum_col2
    FROM table_b b
    INNER JOIN ContractScope cs ON b.user_id = cs.id
    GROUP BY b.user_id
) Table2 ON u.id = Table2.user_id

性能优化提示：上述示例中两个LEFT JOIN子查询均扫描同一张table_b表，可以通过条件聚合合并为一次表扫描，减少IO开销：

WITH ContractScope AS (
    SELECT id FROM (VALUES (1001),(1002),(1003),(1005)) AS c(id)
)
SELECT 
    u.column1,u.column2,u.column3,
    b_agg.sum_col1,
    b_agg.sum_col2
FROM table_u u (NOLOCK)
INNER JOIN ContractScope cs ON u.id = cs.id
LEFT JOIN (
    SELECT 
        b.user_id,
        SUM(CASE WHEN 聚合条件1 THEN b.column END) AS sum_col1,
        SUM(CASE WHEN 聚合条件2 THEN b.column END) AS sum_col2
    FROM table_b b
    INNER JOIN ContractScope cs ON b.user_id = cs.id
    GROUP BY b.user_id
) b_agg ON u.id = b_agg.user_id

方案2：表变量/临时表（适合存储过程内复杂逻辑场景） #

如果contract范围的计算逻辑复杂，需要多步处理才能得到，在存储过程中可以先将范围结果存入表变量或临时表，后续所有逻辑统一引用：

-- 定义范围存储表
DECLARE @ContractScope TABLE (id INT PRIMARY KEY CLUSTERED);
-- 一次性写入符合条件的contract ID
INSERT INTO @ContractScope
SELECT id FROM dim_contract 
WHERE create_time >= '2024-01-01' 
  AND status = 'Active'
  AND region = 'APAC';

-- 后续所有查询直接关联@ContractScope即可，用法和CTE一致
SELECT ...
FROM table_u u
INNER JOIN @ContractScope cs ON u.id = cs.id
LEFT JOIN (...) Table1 ON u.id = Table1.user_id

如果contract ID量级超过1万，推荐使用带索引的临时表#ContractScope替代表变量，能获得更好的查询性能。

方案3：视图（跨多查询/存储过程长期复用场景） #

如果该contract范围是业务层面通用的筛选规则，被多个不同的查询、存储过程复用，可以将范围逻辑封装为视图：

CREATE VIEW vw_ValidContractScope
AS
SELECT id 
FROM dim_contract 
WHERE is_deleted = 0 
  AND is_valid = 1
  AND DATEDIFF(day, GETDATE(), expire_time) > 0
-- 所有范围规则仅需在该视图内维护
GO

后续所有需要用到该范围的查询，直接关联视图即可JOIN vw_ValidContractScope cs ON xxx.id = cs.id，修改范围规则时仅需更新一次视图定义，所有引用该视图的查询会自动同步最新范围，无需逐个修改。

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