SQL CASE 表达式
SQL CASE 表达式
The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE 语法
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;演示数据库
以下是 Northwind 示例数据库中 "OrderDetails" 表的选取
| OrderDetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 10248 | 11 | 12 |
| 2 | 10248 | 42 | 10 |
| 3 | 10248 | 72 | 5 |
| 4 | 10249 | 14 | 9 |
| 5 | 10249 | 51 | 40 |
SQL CASE 示例
以下 SQL 代码遍历条件，并在第一个条件满足时返回一个值
示例
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN '数量大于 30'
WHEN Quantity = 30 THEN '数量为 30'
ELSE '数量小于 30'
END AS QuantityText
FROM OrderDetails;
亲自尝试 »
以下 SQL 代码将根据城市对客户进行排序。但是，如果城市为 NULL，则按国家排序
示例
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
亲自尝试 »
