如何基于列检查多条件？寻求多拒绝条件的SQL优化实现方案

我懂你现在的感受——一堆嵌套的CASE分支写起来繁琐，后续要加新条件更是要改一堆地方，维护成本太高。咱们来重构这段代码，让它更简洁、易扩展，同时保持和原逻辑一致的输出。

原代码回顾 #

先贴一下你原来的实现，方便对比：

declare @tbl table(id int identity, check1 float, check2 float, check3 float)
insert into @tbl (check1, check2, check3) values (10, 15, 20), (5, 9, 8), (2, 3, 10)
select id, case 
when check1 < 5 and check2 < 10 and check3 < 15 then 'check1 is less than 5 and check2 is less than 10 and check3 is less than 15'
when check1 < 5 and check2 < 10 then 'check1 is less than 5 and check2 is less than 10'
when check1 < 5 and check3 < 15 then 'check1 is less than 5 and check3 is less than 15'
when check2 < 10 and check3 < 15 then 'check2 is less than 10 and check3 is less than 15'
when check1 < 5 then 'check1 is less than 5 '
when check2 < 10 then 'check2 is less than 10 '
when check3 < 15 then 'check3 is less than 15 '
else 'ok' 
end from @tbl

优化方案1：使用STRING_AGG（SQL Server 2017+） #

这个方案利用字符串聚合函数，把每个满足的条件单独判断，最后拼接成结果，每个条件只需要写一次，扩展性极强：

declare @tbl table(id int identity, check1 float, check2 float, check3 float)
insert into @tbl (check1, check2, check3) values (10, 15, 20), (5, 9, 8), (2, 3, 10)

select 
    id,
    ISNULL(condition_text, 'ok') as result
from (
    select 
        id,
        STRING_AGG(condition, ' and ') as condition_text
    from (
        -- 逐个判断每个条件，生成符合要求的描述
        select id, CASE WHEN check1 < 5 THEN 'check1 is less than 5' END as condition from @tbl
        union all
        select id, CASE WHEN check2 < 10 THEN 'check2 is less than 10' END as condition from @tbl
        union all
        select id, CASE WHEN check3 < 15 THEN 'check3 is less than 15' END as condition from @tbl
    ) sub
    where condition IS NOT NULL -- 过滤掉不满足的条件
    group by id
) main

优化方案2：使用CONCAT兼容低版本SQL Server #

如果你的SQL Server版本低于2017，没法用STRING_AGG，可以用CONCAT拼接，再处理多余的连接词：

declare @tbl table(id int identity, check1 float, check2 float, check3 float)
insert into @tbl (check1, check2, check3) values (10, 15, 20), (5, 9, 8), (2, 3, 10)

select 
    id,
    CASE 
        WHEN TRIM(condition_text) = '' THEN 'ok'
        -- 去掉开头可能多余的"and "，并清理空格
        ELSE LTRIM(REPLACE(condition_text, 'and ', '', 1))
    END as result
from (
    select 
        id,
        CONCAT(
            CASE WHEN check1 < 5 THEN 'check1 is less than 5 ' ELSE '' END,
            CASE WHEN check2 < 10 THEN 'and check2 is less than 10 ' ELSE '' END,
            CASE WHEN check3 < 15 THEN 'and check3 is less than 15 ' ELSE '' END
        ) as condition_text
    from @tbl
) sub

方案优势 #

• 减少冗余：每个条件只需要定义一次，不用重复写多个组合分支
• 易扩展：新增判断条件时，只需要在子查询里加一行union all（方案1）或者加一个CASE（方案2），不用修改大量分支
• 逻辑清晰：把条件判断和结果拼接分离，可读性更强
• 输出一致：两种方案的输出结果和你原代码完全一致，比如第三行数据会返回三个条件的组合描述

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