基于多过滤条件高效查询SQL表的方案咨询（无需Union）

数据准备 #

DECLARE @Table AS TABLE
    (
        [Id]        INT IDENTITY(1, 1)
      , [Type]      TINYINT
      , [TypeOne]   INT
      , [TypeTwo]   INT
      , [TypeThree] INT
    ) ;

INSERT INTO @Table
    ( [Type]
    , [TypeOne]
    , [TypeTwo]
    , [TypeThree] )
VALUES
    ( 1, 1, NULL, NULL )
  , ( 1, 2, NULL, NULL )
  , ( 1, 3, NULL, NULL )
  , ( 2, NULL, 10, NULL )
  , ( 2, NULL, 20, NULL )
  , ( 3, NULL, NULL, 100 )
  , ( 3, NULL, NULL, 200 )
  , ( 3, NULL, NULL, 300 ) ;

查询过滤条件 #

DECLARE @IncludeTypeOne BIT = 1
      , @IncludeTypeTwo BIT = 0 ;

DECLARE @TypeThree_Ids TABLE ( [TypeThree] INT ) ;
INSERT INTO @TypeThree_Ids
VALUES
    ( 200 )
  , ( 300 ) ;

需求目标 #

基于@IncludeTypeOne、@IncludeTypeTwo和@TypeThree_Ids的值查询@Table表：

• 若@IncludeTypeOne为1，保留所有Type=1的行，无需校验TypeOne具体值；若为0，排除所有Type=1的行
• 若@IncludeTypeTwo为1，保留所有Type=2的行，无需校验TypeTwo具体值；若为0，排除所有Type=2的行
• 对于Type=3的行，仅保留TypeThree值存在于@TypeThree_Ids中的行；若@TypeThree_Ids为空，排除所有Type=3的行

实际表数据量庞大，要求不能通过三次单独查询再用Union拼接的方式实现。

预期输出 #

Id  Type    TypeOne TypeTwo TypeThree
1   1       1       NULL    NULL
2   1       2       NULL    NULL
3   1       3       NULL    NULL
7   3       NULL    NULL    200
8   3       NULL    NULL    300

失败尝试 #

SELECT  *
FROM    @Table
WHERE   ( ( @IncludeTypeOne = 0 AND [Type] <> 1 ) OR [Type] = 1 )
        AND ( ( @IncludeTypeTwo = 0 AND [Type] <> 2 ) OR [Type] = 2 )
        AND ( ( ( SELECT COUNT(1) FROM @TypeThree_Ids ) = 0 AND [Type] <> 3 ) OR [TypeThree] IN ( SELECT [TypeThree] FROM @TypeThree_Ids ) ) ;

实际输出 #

Id  Type    TypeOne TypeTwo TypeThree

解决方案 #

你失败的核心原因是用了**逻辑与（AND）组合所有条件，而实际上不同类型的筛选应该是逻辑或（OR）**的关系——我们需要保留符合Type=1且开启该类型、Type=3且匹配ID的行，同时排除Type=2的行（因为@IncludeTypeTwo=0）。

以下是高效的单表扫描实现方式：

SELECT *
FROM @Table
WHERE 
    -- 保留Type=1的行（仅当开启该类型时）
    (@IncludeTypeOne = 1 AND [Type] = 1)
    -- 保留Type=2的行（仅当开启该类型时）
    OR (@IncludeTypeTwo = 1 AND [Type] = 2)
    -- 保留Type=3且TypeThree在@TypeThree_Ids中的行
    OR ([Type] = 3 AND EXISTS (SELECT 1 FROM @TypeThree_Ids t WHERE t.TypeThree = @Table.TypeThree))

逻辑说明 #

1. Type=1分支：仅当@IncludeTypeOne=1时生效，直接选中所有Type=1的行
2. Type=2分支：仅当@IncludeTypeTwo=1时生效（当前示例中该条件为false，无数据返回）
3. Type=3分支：用EXISTS关联临时表，只匹配存在的TypeThree值，避免IN子句可能的性能问题；当@TypeThree_Ids为空时，该分支自动不生效，不会返回Type=3的行

此写法仅需一次表扫描，完全符合大数据量场景的性能要求。

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