如何让T-SQL代码匹配首个条件后停止判断，仅返回单一结果

需要实现顺序条件判断逻辑：按顺序检查6个层级的条件，一旦某个条件为真，立即返回对应结果并停止后续条件检查；若所有条件都不匹配，返回默认值。

当前使用嵌套IIF或CASE语句时出现异常，本质是代码存在拼写/语法错误，而非语句本身的逻辑问题。

现有代码问题 #

嵌套IIF代码问题 #

IIF((t.testkey = 141  AND wordsalad <3) OR (t.testkey = 141 AND wordsalad>10),'Found it 1',
iif(t.testkey = 821 AND wordsalad <20,'Found it 2',
    iif(t.testkey = 725 AND wordsalad<1.0030,'Fouind it 3', -- 拼写错误：Fouind → Found
        iif(t.testkey = 725 AND wordsalad>1.025,'Found it 4',
            iif(t.testkey = 810 AND wordsalad<10,'Found it 5',
                iif(t.testkey = 809 AND word salad<10,'found it 6', -- 语法错误：列名不应有空格
                    'Didn't find it')))))),

CASE语句代码问题 #

CASE
WHEN (t.testkey = 141 AND wordsalad <3) OR (t.testkey = 141  AND 
wordsalad >10.0)) then 'Found it 1' -- 多余的右括号
WHEN (t.testkey = 821 AND wordsald <20) then 'Found it 2' -- 拼写错误：wordsald → wordsalad
WHEN (t.testkey = 725 AND wordsalad <1.0030) then 'Found it 3'
WHEN (t.testkey = 725 AND wordsalad >1.025) then 'Found it 4'
WHEN (t.testkey = 810 AND wordsalad <10) then 'Found it 5'
WHEN (t.testkey = 809 AND wordsalad <10) then 'Found it 6'
Else 'Didn't find it'
END AS [Conditions2]

正确实现方案 #

SQL的CASE语句天生支持短路求值：按顺序匹配WHEN子句，找到第一个满足条件的就返回结果，不会继续检查后续条件。只需修正现有代码中的语法/拼写错误即可：

修正后的CASE语句 #

CASE
    WHEN t.testkey = 141 AND (wordsalad < 3 OR wordsalad > 10.0) THEN 'Found it 1'
    WHEN t.testkey = 821 AND wordsalad < 20 THEN 'Found it 2'
    WHEN t.testkey = 725 AND wordsalad < 1.0030 THEN 'Found it 3'
    WHEN t.testkey = 725 AND wordsalad > 1.025 THEN 'Found it 4'
    WHEN t.testkey = 810 AND wordsalad < 10 THEN 'Found it 5'
    WHEN t.testkey = 809 AND wordsalad < 10 THEN 'Found it 6'
    ELSE 'Didn''t find it' -- 单引号转义处理
END AS [Conditions2]

关键修正点 #

• 移除第一个WHEN子句中多余的右括号
• 修正列名拼写错误：wordsald→wordsalad
• 转义字符串中的单引号：'Didn't'→'Didn''t'
• 合并第一个条件中重复的testkey = 141判断，提升代码可读性

测试验证 #

使用提供的测试数据：

执行修正后的CASE语句会得到以下结果：

• Test1：匹配第一个条件，返回Found it 1
• Test2：匹配第二个条件，返回Found it 2
• Test3：匹配第三个条件，返回Found it 3

所有结果均为首个匹配的条件，不会触发后续判断。

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