如何利用`CASE`语句实现复杂的条件逻辑？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

利用 `CASE` 语句实现复杂的条件逻辑

大家好，今天我们来深入探讨 CASE 语句，这是 SQL 中实现复杂条件逻辑的一个非常强大的工具。不仅仅是简单的 if-else 替代品，CASE 语句可以处理多重条件判断，数据转换，以及基于不同条件返回不同的结果。我们将通过实际例子，逐步了解 CASE 语句的各种用法及其优势。

1. CASE 语句的基本语法

CASE 语句有两种主要形式：简单 CASE 语句和搜索 CASE 语句。

1.1 简单 CASE 语句

简单 CASE 语句将一个表达式与多个可能的值进行比较。其基本语法如下：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE resultN]
END

expression: 要进行比较的表达式。
value1, value2, …: 用于与 expression 进行比较的值。
result1, result2, …: 与 value1, value2, … 相对应的结果。
ELSE resultN: 可选子句，当 expression 与所有 value 都不匹配时，返回 resultN。如果没有 ELSE 子句，并且没有匹配的值，则返回 NULL。

示例：根据城市设置邮费

假设我们有一个 Orders 表，其中包含订单信息，包括 OrderID，CustomerID 和 City。我们想根据订单的城市设置不同的邮费。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    City VARCHAR(255)
);

INSERT INTO Orders (OrderID, CustomerID, City) VALUES
(1, 101, 'New York'),
(2, 102, 'Los Angeles'),
(3, 103, 'Chicago'),
(4, 104, 'New York'),
(5, 105, 'Houston');

SELECT
    OrderID,
    City,
    CASE City
        WHEN 'New York' THEN 5.00
        WHEN 'Los Angeles' THEN 7.50
        WHEN 'Chicago' THEN 6.00
        ELSE 8.00  -- 默认邮费
    END AS ShippingCost
FROM
    Orders;

这个查询将返回每笔订单的 OrderID、City 和基于 City 计算出的 ShippingCost。如果订单的城市是 ‘New York’，邮费为 5.00；如果是 ‘Los Angeles’，邮费为 7.50；如果是 ‘Chicago’，邮费为 6.00；否则，邮费为 8.00。

1.2 搜索 CASE 语句

搜索 CASE 语句允许使用更复杂的条件，例如比较运算符、逻辑运算符等。其基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE resultN]
END

condition1, condition2, …: 布尔表达式，用于判断是否满足条件。
result1, result2, …: 与 condition1, condition2, … 相对应的结果。
ELSE resultN: 可选子句，当所有 condition 都不满足时，返回 resultN。如果没有 ELSE 子句，并且没有满足的条件，则返回 NULL。

示例：根据订单金额设置折扣

假设我们想根据订单金额的不同范围设置不同的折扣。

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    Amount DECIMAL(10, 2)
);

INSERT INTO OrderDetails (OrderDetailID, OrderID, Amount) VALUES
(1, 1, 50.00),
(2, 2, 120.00),
(3, 3, 250.00),
(4, 4, 75.00),
(5, 5, 500.00);

SELECT
    OrderDetailID,
    Amount,
    CASE
        WHEN Amount < 100 THEN 0.00
        WHEN Amount >= 100 AND Amount < 200 THEN 0.05
        WHEN Amount >= 200 AND Amount < 500 THEN 0.10
        ELSE 0.15
    END AS DiscountRate
FROM
    OrderDetails;

这个查询将返回每笔订单的 OrderDetailID、Amount 和基于 Amount 计算出的 DiscountRate。如果订单金额小于 100，折扣率为 0.00；如果订单金额在 100 到 200 之间，折扣率为 0.05；如果订单金额在 200 到 500 之间，折扣率为 0.10；否则，折扣率为 0.15。

2. CASE 语句的应用场景

CASE 语句在 SQL 中有很多应用场景，下面列举一些常见的例子：

2.1 数据转换

CASE 语句可以用于将数据从一种格式转换为另一种格式。例如，将数字代码转换为对应的文本描述。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductType INT
);

INSERT INTO Products (ProductID, ProductType) VALUES
(1, 1),
(2, 2),
(3, 1),
(4, 3);

SELECT
    ProductID,
    CASE ProductType
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Home Goods'
        ELSE 'Unknown'
    END AS ProductCategory
FROM
    Products;

在这个例子中，CASE 语句将 ProductType 列中的数字代码转换为对应的产品类别描述。

2.2 条件聚合

CASE 语句可以与聚合函数一起使用，以实现条件聚合。例如，计算不同类别的订单数量。

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleDate DATE,
    Quantity INT
);

INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity) VALUES
(1, 1, '2023-01-01', 10),
(2, 2, '2023-01-01', 5),
(3, 1, '2023-01-02', 8),
(4, 2, '2023-01-02', 12),
(5, 3, '2023-01-03', 7);

SELECT
    SUM(CASE WHEN ProductID = 1 THEN Quantity ELSE 0 END) AS Product1Sales,
    SUM(CASE WHEN ProductID = 2 THEN Quantity ELSE 0 END) AS Product2Sales,
    SUM(CASE WHEN ProductID = 3 THEN Quantity ELSE 0 END) AS Product3Sales
FROM
    Sales;

在这个例子中，CASE 语句用于分别计算 ProductID 为 1、2 和 3 的产品的总销售量。

2.3 数据验证

CASE 语句可以用于数据验证，以确保数据的完整性。例如，检查年龄是否在合理范围内。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Age INT
);

INSERT INTO Customers (CustomerID, Age) VALUES
(1, 25),
(2, 150),
(3, -5),
(4, 30);

SELECT
    CustomerID,
    Age,
    CASE
        WHEN Age < 0 OR Age > 120 THEN 'Invalid Age'
        ELSE 'Valid Age'
    END AS AgeStatus
FROM
    Customers;

在这个例子中，CASE 语句用于检查 Age 列的值是否在 0 到 120 之间，如果超出范围，则标记为 ‘Invalid Age’。

2.4 行列转换

CASE 语句可以用于将行数据转换为列数据，这在生成报表时非常有用。假设我们有一个 SurveyResponses 表，其中包含调查问卷的回答。

CREATE TABLE SurveyResponses (
    ResponseID INT PRIMARY KEY,
    QuestionID INT,
    Answer VARCHAR(255)
);

INSERT INTO SurveyResponses (ResponseID, QuestionID, Answer) VALUES
(1, 1, 'Yes'),
(2, 2, 'No'),
(3, 1, 'No'),
(4, 2, 'Yes');

-- QuestionID 1: Do you like SQL?
-- QuestionID 2: Do you like Python?

SELECT
    SUM(CASE WHEN QuestionID = 1 THEN 1 ELSE 0 END) AS Question1Yes, -- 统计回答 Question 1 为 Yes 的数量
    SUM(CASE WHEN QuestionID = 2 THEN 1 ELSE 0 END) AS Question2Yes  -- 统计回答 Question 2 为 Yes 的数量
FROM
    SurveyResponses
WHERE Answer = 'Yes';  -- 只统计回答为 Yes 的情况

这个例子统计了有多少人对SQL和Python的调查问卷回答为 ‘Yes’。

3. CASE 语句的嵌套使用

CASE 语句可以嵌套使用，以实现更复杂的条件逻辑。

示例：根据订单金额和客户类型设置折扣

假设我们想根据订单金额和客户类型设置不同的折扣。

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerType VARCHAR(255) -- 'Regular', 'Premium'
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Amount DECIMAL(10, 2)
);

INSERT INTO Customers (CustomerID, CustomerType) VALUES
(1, 'Regular'),
(2, 'Premium');

INSERT INTO Orders (OrderID, CustomerID, Amount) VALUES
(1, 1, 50.00),
(2, 1, 120.00),
(3, 2, 250.00),
(4, 2, 75.00);

SELECT
    OrderID,
    CustomerID,
    Amount,
    CASE
        WHEN CustomerType = 'Premium' THEN
            CASE
                WHEN Amount < 100 THEN 0.05
                WHEN Amount >= 100 AND Amount < 200 THEN 0.10
                ELSE 0.15
            END
        ELSE
            CASE
                WHEN Amount < 100 THEN 0.00
                WHEN Amount >= 100 AND Amount < 200 THEN 0.05
                ELSE 0.10
            END
    END AS DiscountRate
FROM
    Orders o
JOIN
    Customers c ON o.CustomerID = c.CustomerID;

在这个例子中，外层的 CASE 语句根据客户类型选择不同的折扣方案。如果客户是 ‘Premium’ 类型，则使用更高的折扣率。内层的 CASE 语句则根据订单金额设置具体的折扣率。

4. CASE 语句与 COALESCE 函数

COALESCE 函数返回其参数列表中第一个非 NULL 的表达式。 CASE 语句可以与 COALESCE 函数结合使用，以处理 NULL 值。

示例：优先使用客户提供的电话号码，如果没有则使用默认电话号码

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    PhoneNumber VARCHAR(20),
    DefaultPhoneNumber VARCHAR(20)
);

INSERT INTO Customers (CustomerID, PhoneNumber, DefaultPhoneNumber) VALUES
(1, '123-456-7890', '987-654-3210'),
(2, NULL, '987-654-3210');

SELECT
    CustomerID,
    COALESCE(PhoneNumber, DefaultPhoneNumber, 'N/A') AS ContactNumber
FROM
    Customers;

在这个例子中，COALESCE 函数首先检查 PhoneNumber 是否为 NULL，如果不是 NULL，则返回 PhoneNumber 的值；否则，检查 DefaultPhoneNumber 是否为 NULL，如果不是 NULL，则返回 DefaultPhoneNumber 的值；如果两者都为 NULL，则返回 ‘N/A’。这个功能也可以通过 CASE 语句实现：

SELECT
    CustomerID,
    CASE
        WHEN PhoneNumber IS NOT NULL THEN PhoneNumber
        WHEN DefaultPhoneNumber IS NOT NULL THEN DefaultPhoneNumber
        ELSE 'N/A'
    END AS ContactNumber
FROM
    Customers;

5. CASE 语句与 NULL 值的处理

需要特别注意的是，在 CASE 语句中处理 NULL 值时，需要使用 IS NULL 或 IS NOT NULL 运算符，而不能使用 = 或 != 运算符。

示例：根据地址是否为空设置地址状态

CREATE TABLE Addresses (
    AddressID INT PRIMARY KEY,
    AddressLine1 VARCHAR(255)
);

INSERT INTO Addresses (AddressID, AddressLine1) VALUES
(1, '123 Main St'),
(2, NULL);

SELECT
    AddressID,
    CASE
        WHEN AddressLine1 IS NULL THEN 'Address Not Provided'
        ELSE 'Address Provided'
    END AS AddressStatus
FROM
    Addresses;

在这个例子中，CASE 语句使用 IS NULL 运算符检查 AddressLine1 列是否为 NULL。

6. CASE 语句的性能考虑

虽然 CASE 语句非常灵活，但在某些情况下，过度使用 CASE 语句可能会影响查询性能。特别是在处理大量数据时，复杂的 CASE 语句可能会导致查询执行时间较长。因此，在编写 SQL 查询时，需要权衡 CASE 语句的灵活性和性能。

以下是一些可以提高 CASE 语句性能的建议：

尽量避免在 WHERE 子句中使用复杂的 CASE 语句。可以考虑将 CASE 语句移到 SELECT 子句中。
如果可能，使用索引来加速 CASE 语句的评估。
对于非常复杂的条件逻辑，可以考虑使用存储过程或用户自定义函数来提高性能。
分析查询执行计划，找到性能瓶颈，并进行优化。

7. 一些需要注意的细节

CASE 表达式必须以 END 关键字结尾。
CASE 表达式中可以包含多个 WHEN 子句。
CASE 表达式中可以包含一个可选的 ELSE 子句。如果没有 ELSE 子句，并且没有满足的条件，则返回 NULL。
CASE 表达式可以嵌套使用。
CASE 表达式的返回值类型由 THEN 子句中的表达式的类型决定。所有 THEN 子句的表达式必须具有兼容的类型。

8. 总结：灵活运用 CASE 语句处理复杂的条件

今天我们学习了 CASE 语句的基本语法、应用场景和性能考虑。 CASE 语句是 SQL 中一个非常强大的工具，可以用于实现复杂的条件逻辑。通过灵活运用 CASE 语句，我们可以编写出更加简洁、高效的 SQL 查询。希望大家能在实际工作中多多实践，熟练掌握 CASE 语句的使用技巧。

利用 CASE 语句实现复杂的条件逻辑

发表回复 取消回复

利用 `CASE` 语句实现复杂的条件逻辑

发表回复取消回复