针对形如if-then-else
的条件语句,有多种方法可供选择,每一种方法在可读性和性能方面都有所不同。所有方法也可以通过将true值(1
)用于else分支(因为if p then q
在逻辑上等价于if p then q else true
)来处理if-then
的情况。
(注意:问题代码示例中显示的条件断言当@Closed
为真时,closedDate
应为空;这似乎是一个逻辑错误,因为@Closed
应该为真以获取已关闭订单,在这种情况下,正如问题文本正确地指出的那样,closedDate
不应为空。在本答案中,NULL
测试被交换以纠正此错误。)
IF
函数
一些RDBMS具有IF
函数,它最接近于问题示例并且概念上最简单。
IF(<test>, <then condition>, <else condition>)
SQL Server提供了IIF
,它相当于一个CASE
表达式(见下文)。由于@Close
基本上包含一个布尔值,因此可以直接测试它,而不是与1
进行比较。在更一般的情况下,将使用比较表达式。
IF(@Closed, o.ClosedDate IS NOT NULL, o.ClosedDate IS NULL)
请注意,由于这使用了函数中的列值,因此使用IF
的查询通常不是sargable。
CASE
运算符
IF
函数相当简单:在两个备选项之间选择一个测试。CASE
表达式是更一般的,允许任意数量的备选项。因此,您可以选择要处理为备选项的值。
CASE
有两种形式,简单和搜索。在简单的CASE
中,将顶部的值与列出的每个备选值进行比较。在搜索的CASE
中,每个备选项都有自己的表达式,这些表达式独立地作为布尔值进行评估。
-- simple: compare top <expression> to each WHEN <expression>
CASE <expression>
WHEN <expression> THEN ...
...
ELSE ...
END
-- searched: test each <expression>
CASE
WHEN <expression> THEN ...
...
ELSE ...
END
当用于条件语句时,搜索表达式是最简单的,并且与 if-then-else
相对应。对于所讨论的情况:
CASE
WHEN @Closed THEN o.ClosedDate IS NOT NULL
ELSE o.ClosedDate IS NULL
END
如果测试比布尔变量更复杂,选择一个搜索表达式会更有意义。如果测试是一个你要比较的单个值,那么简单形式可能是有意义的,尽管你必须决定如何处理超出预期值的情况。你可以明确列出所有预期的值,并将其他值视为通过。
CASE @Closed
WHEN 1 THEN o.ClosedDate IS NOT NULL
WHEN 0 THEN o.ClosedDate IS NULL
ELSE 1
END
你也可以将意外的值视为失败或引发错误。
你可以处理一个值,将所有其他值视为替代方案:
CASE @Closed
WHEN 1 THEN o.ClosedDate IS NOT NULL
ELSE o.ClosedDate IS NULL
END
CASE @Closed
WHEN 0 THEN o.ClosedDate IS NULL
ELSE o.ClosedDate IS NOT NULL
END
选择哪个取决于业务规则。
CASE
表达式通常不是可搜索的,特别是它们经常涉及从列计算值(尽管您应该始终检查查询执行计划以确保)。
逻辑运算符
逻辑推论,如果p,则q
(在逻辑上也写作p意味着q
或p⇒q
),在逻辑上等同于非p或q
(在逻辑上也写作¬p∨q
)。没有标准的方法来表达逻辑三元表达式,如果p则q否则r
(在编程中,p?q:r
)用其他逻辑运算符,但有两个强有力的候选者:
(如果p那么q)且(如果非p那么r)
,(p ⇒ q) ∧ (¬p ⇒ q)
(如果p那么q且非r)且(如果非p那么非q且r)
,(p ⇒ q∧¬r) ∧ (¬p ⇒ ¬q∧r)
这两个公式都可以作为用其他逻辑运算符重写if-then-else
的基础:
(NOT <test> OR <then condition>)
AND ( <test> OR <else condition>)
(NOT <test> OR ( <then condition> AND NOT <else condition>))
AND ( <test> OR (NOT <then condition> AND <else condition>)
这种写法的可读性不如IF()
或CASE
,但潜在的性能更好。
对于这个问题,由于q
和r
是相互依赖的,因此只有第一种解释值得考虑。
(@Closed OR o.ClosedDate IS NOT NULL) AND (NOT @Closed OR o.ClosedDate IS NULL)
留给读者作为练习的是证明这归结为适当条件的过程。
由于这个表达式是简单比较的组合,很可能是可搜索的(但是,再次检查查询执行计划)。
IF
和 CASE
语句
在某些上下文中(例如存储过程和函数),可以使用 IF
或 CASE
语句在不同的查询之间进行选择。与 IF
函数不同,IF
语句和 CASE
允许多于 2 个备选项(尽管此处不需要该功能)。
IF <test> THEN
SELECT ... WHERE <then condition> ...
ELSE
SELECT ... WHERE <else condition> ...
END IF
CASE
语句与CASE
运算符不同,两者的区别在于:
- 它们出现的语法上下文不同(大致上,语句用于过程中,运算符用于查询中),
- 分支中允许的内容不同(大致上,语句允许查询,运算符允许表达式),以及
- 语句以
END CASE
结束。
CASE
语句与IF
语句非常相似,特别是在搜索形式中:
CASE
WHEN <test> THEN
SELECT ... WHERE <then condition> ...
ELSE
SELECT ... WHERE <else condition> ...
END CASE
这些语句更冗长,涉及重复代码(这是引入错误的机会),但如果性能是一个问题,它们是最可搜索的选项。
针对所讨论的示例:
IF @Closed THEN
SELECT *
FROM Orders o
WHERE o.OrderDate BETWEEN @startDate AND @endDate
AND o.ClosedDate IS NOT NULL;
ELSE
SELECT *
FROM Orders o
WHERE o.OrderDate BETWEEN @startDate AND @endDate
AND o.ClosedDate IS NULL;
END IF;