SQL入门之CASE函数

一、什么是 CASE 函数？

CASE 是 SQL 中的一个条件表达式，类似于编程语言中的 if-else 语句。它允许你根据特定条件返回不同的值。CASE 可以在 SELECT 查询中用来对数据进行分类、计算或者转换，也可以用于 UPDATE 和 DELETE 操作中。

二、CASE 函数的基本语法

CASE 函数有两种基本的语法形式：

1. 简单 CASE 表达式

CASE <expression>
    WHEN <value1> THEN <result1>
    WHEN <value2> THEN <result2>
    ELSE <result3>
END

• <expression>：你想要比较的字段或表达式。
• WHEN <value>：如果 <expression> 的值等于 <value>，则返回相应的 <result>。
• ELSE：如果没有匹配的条件，返回默认的结果（可选）。

2. 搜索 CASE 表达式

CASE
    WHEN <condition1> THEN <result1>
    WHEN <condition2> THEN <result2>
    ELSE <result3>
END

• 适用于当条件较复杂时，可以直接在 WHEN 后面写条件判断。

三、CASE 函数的应用场景

• 数据分类：基于某些条件对数据进行分类或标记。
• 条件计算：根据条件进行计算，比如根据成绩给学生打等级、根据工资计算奖金等。
• 数据转换：在查询结果中进行数据的转换，映射或格式化。

四、CASE 函数的演示案例

1. 基本示例：根据成绩评定等级

假设有一张学生成绩表 students，其包含字段 student_id（学生ID），name（学生姓名），score（学生成绩）。

CREATE TABLE students (
    student_id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO students VALUES
(1, 'Alice', 95),
(2, 'Bob', 82),
(3, 'Charlie', 60),
(4, 'David', 48);

我们希望根据成绩给学生分配等级：

• 成绩 >= 90：等级为 A
• 80 <= 成绩 < 90：等级为 B
• 60 <= 成绩 < 80：等级为 C
• 成绩 < 60：等级为 D

SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END AS grade
FROM 
    students;

输出结果：

name	score	grade
Alice	95	A
Bob	82	B
Charlie	60	C
David	48	D

2. 示例：根据订单金额计算折扣

假设有一张订单表 orders，其包含字段 order_id（订单ID），amount（订单金额）。

我们希望根据订单金额为不同金额的订单提供不同的折扣：

• 订单金额 >= 1000：折扣 20%
• 500 <= 订单金额 < 1000：折扣 10%
• 订单金额 < 500：折扣 5%

CREATE TABLE orders (
    order_id INT,
    amount DECIMAL(10, 2)
);

INSERT INTO orders VALUES
(1, 1200.00),
(2, 800.00),
(3, 400.00);

查询订单金额及相应的折扣：

SELECT 
    order_id,
    amount,
    CASE
        WHEN amount >= 1000 THEN amount * 0.2
        WHEN amount >= 500 THEN amount * 0.1
        ELSE amount * 0.05
    END AS discount
FROM 
    orders;

输出结果：

order_id	amount	discount
1	1200.00	240.00
2	800.00	80.00
3	400.00	20.00

3. 示例：计算员工奖金

假设有一张员工表 employees，其包含字段 employee_id（员工ID），name（员工姓名），salary（员工工资）。

我们希望根据员工的工资发放奖金：

• 工资 >= 8000：奖金 2000 元
• 5000 <= 工资 < 8000：奖金 1000 元
• 工资 < 5000：奖金 500 元

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Eve', 9000.00),
(2, 'John', 6000.00),
(3, 'Mary', 4500.00);

查询员工姓名、工资及奖金：

SELECT 
    name,
    salary,
    CASE
        WHEN salary >= 8000 THEN 2000
        WHEN salary >= 5000 THEN 1000
        ELSE 500
    END AS bonus
FROM 
    employees;

输出结果：

name	salary	bonus
Eve	9000.00	2000
John	6000.00	1000
Mary	4500.00	500

4. 示例：处理 NULL 值

假设我们有一个订单表 orders，其包含字段 order_id（订单ID）和 delivery_date（配送日期）。如果订单尚未配送，则 delivery_date 为 NULL。

我们希望输出一个状态列：

• 如果 delivery_date 为 NULL，则显示 'Not Delivered'
• 否则显示 'Delivered'

CREATE TABLE orders (
    order_id INT,
    delivery_date DATE
);

INSERT INTO orders VALUES
(1, '2023-10-01'),
(2, NULL),
(3, '2023-10-03');

查询订单ID和配送状态：

SELECT 
    order_id,
    CASE
        WHEN delivery_date IS NULL THEN 'Not Delivered'
        ELSE 'Delivered'
    END AS status
FROM 
    orders;

输出结果：

order_id	status
1	Delivered
2	Not Delivered
3	Delivered

五、CASE 函数的高级用法

1. 嵌套 CASE 表达式：可以在 WHEN 或 ELSE 中嵌套另一个 CASE 表达式。

   示例：

   CASE
       WHEN score >= 90 THEN 'A'
       WHEN score >= 80 THEN 
           CASE
               WHEN score >= 85 THEN 'B+'
               ELSE 'B'
           END
       ELSE 'C'
   END AS grade
   

2. CASE 与 GROUP BY 一起使用：在 GROUP BY 子句中使用 CASE 来对分组进行条件判断。

3. CASE 用于 ORDER BY：可以根据 CASE 结果进行排序。

   示例：

   SELECT 
       name,
       score
   FROM 
       students
   ORDER BY 
       CASE
           WHEN score >= 90 THEN 1
           WHEN score >= 80 THEN 2
           ELSE 3
       END;
   

六、总结

• CASE 是 SQL 中的一个条件表达式，类似于 if-else 语句。
• 它可以用于数据分类、计算和转换。
• 通过 CASE，可以轻松实现复杂的条件逻辑。
• 学会合理使用 CASE 函数，将使你的 SQL 查询更加灵活、强大。