【MySQL入门·查询进阶】8.3 CASE

原创已于 2024-05-12 15:08:28 修改 · 1.8k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #CASE语法 #ORDER BY使用CASE #嵌套CASE语句 #简单CASE表达式 #搜索CASE表达式

于 2024-05-11 23:10:45 首次发布

《跟老吕学MySQL》专栏收录该内容

144 篇文章

订阅专栏

本文详细介绍了MySQL中的CASE语句，包括简单CASE和搜索CASE的语法及示例，展示了如何在SELECT、UPDATE、DELETE语句中使用CASE。还探讨了嵌套CASE和在ORDER BY中使用CASE的高级应用，以及使用CASE时应注意的性能和可读性问题。

【MySQL入门·查询进阶】8.3 CASE

CASE

在MySQL中，CASE语句是一种条件表达式，它允许你根据一系列的条件来返回不同的值。CASE语句在SQL查询中非常有用，特别是当你需要根据某个字段的多个可能值来返回不同的结果时。下面我们将详细探讨MySQL中CASE语句的语法和使用方法。

语法

MySQL中的CASE语句有两种形式：简单CASE表达式和搜索CASE表达式。

1. 简单CASE表达式

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE result
END

在这个形式中，expression是要被评估的表达式，value1, value2, … 是可能的值，而 result1, result2, … 是当对应值匹配时返回的结果。如果所有的WHEN子句都不匹配，那么将返回ELSE子句中的result。如果没有ELSE子句，并且没有WHEN子句匹配，那么CASE表达式将返回NULL。

2. 搜索CASE表达式

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

在搜索CASE表达式中，condition1, condition2, … 是要评估的条件，而 result1, result2, … 是当对应条件为真时返回的结果。与简单CASE表达式类似，如果没有任何WHEN子句的条件为真，并且存在ELSE子句，那么将返回ELSE子句中的result。否则，返回NULL。

示例

示例1：使用简单CASE表达式

假设我们有一个名为employees的表，其中包含一个名为department的字段，表示员工的部门。我们想要根据部门字段的值返回不同的描述：

SELECT name,
       department,
       CASE department
           WHEN 'IT' THEN '信息技术部'
           WHEN 'HR' THEN '人力资源部'
           WHEN 'Sales' THEN '销售部'
           ELSE '其他部门'
       END AS department_description
FROM employees;

在这个查询中，我们根据department字段的值使用CASE表达式返回对应的中文描述。

示例2：使用搜索CASE表达式

考虑一个销售表sales_records，它有一个amount字段表示销售额。我们想要根据销售额的大小返回不同的评级：

SELECT sale_id,
       amount,
       CASE
           WHEN amount < 100 THEN '低销售额'
           WHEN amount BETWEEN 100 AND 500 THEN '中等销售额'
           WHEN amount > 500 THEN '高销售额'
           ELSE '未知'
       END AS sales_rating
FROM sales_records;

在这个查询中，我们使用搜索CASE表达式根据amount字段的值返回不同的销售额评级。

示例3：在UPDATE语句中使用CASE

假设我们想要根据员工的部门来更新他们的薪资。例如，我们想要给IT部门的员工加薪10%，给销售部的员工加薪5%，而其他部门的员工薪资保持不变。我们可以使用UPDATE语句结合CASE表达式来实现这一需求：

UPDATE employees
SET salary = CASE department
    WHEN 'IT' THEN salary * 1.10
    WHEN 'Sales' THEN salary * 1.05
    ELSE salary
END;

在这个示例中，我们根据department字段的值使用CASE表达式计算新的薪资，并通过UPDATE语句将结果更新到salary字段中。

示例4：在DELETE语句中使用CASE

虽然CASE语句在DELETE语句中的使用相对较少，但有时候我们可能需要根据某些条件来决定是否删除记录。例如，假设我们想要删除销售额低于100且部门不是IT的记录：

DELETE FROM sales_records
WHERE CASE
    WHEN amount < 100 AND department <> 'IT' THEN 1
    ELSE 0
END = 1;

这个查询的逻辑是，只有当amount小于100且department不是’IT’时，CASE表达式的结果才为1，此时DELETE语句才会执行删除操作。

注意事项

在使用CASE语句时，需要注意以下几点：

性能考虑：虽然CASE语句提供了强大的条件判断能力，但在处理大量数据时，它可能会增加查询的复杂度，从而影响性能。因此，在性能敏感的场景中，应谨慎使用CASE语句，并考虑其他可能的优化方案。
可读性：复杂的CASE语句可能会降低代码的可读性。为了提高代码的可维护性，建议将复杂的逻辑拆分成多个简单的CASE语句或使用存储过程等结构来封装逻辑。
NULL处理：当使用简单CASE表达式时，请注意表达式和值的比较结果可能会因为NULL值的存在而变得复杂。因为NULL与任何值的比较结果都是UNKNOWN，所以在涉及NULL的比较时，需要使用IS NULL或IS NOT NULL来确保正确的结果。

高级应用

1. 嵌套CASE语句

在MySQL中，CASE语句可以嵌套使用，允许你创建更复杂的条件逻辑。嵌套CASE语句在处理具有多个层次或相互依赖的条件时非常有用。

例如，假设你有一个包含员工信息的表，你想根据员工的薪资水平和职位来分类员工。你可以使用嵌套CASE语句来实现这个逻辑：

SELECT 
    employee_id,
    position,
    salary,
    CASE 
        WHEN position = Manager THEN
            CASE 
                WHEN salary > 10000 THEN High-paid Manager
                WHEN salary BETWEEN 7000 AND 10000 THEN Mid-paid Manager
                ELSE Low-paid Manager
            END
        ELSE
            CASE 
                WHEN salary > 8000 THEN High-paid Employee
                WHEN salary BETWEEN 5000 AND 8000 THEN Mid-paid Employee
                ELSE Low-paid Employee
            END
    END AS employee_category
FROM employees;

这个查询根据员工的职位和薪资水平，将员工分类为不同的类别。

2. 在ORDER BY中使用CASE语句

CASE语句也可以在ORDER BY子句中使用，以实现根据特定条件对结果进行排序。这对于需要非标准排序顺序的场景非常有用。

例如，假设你想根据订单状态对订单进行排序，但希望将pending状态的订单放在最后：

SELECT 
    order_id,
    status
FROM orders
ORDER BY 
    CASE status
        WHEN pending THEN 2
        ELSE 1
    END, status;

在这个例子中，CASE语句在ORDER BY子句中用于为每个订单状态分配一个排序权重。pending状态的订单被赋予较高的权重，因此它们会排在其他状态之后。最后，通过添加status作为次要排序条件，确保其他状态的订单按字母顺序排序。

注意事项

1. 性能考虑

虽然CASE语句在MySQL中非常有用，但在某些情况下，过度使用或不当使用可能会对性能产生负面影响。特别是当CASE语句在大数据集上运行时，它可能会导致查询执行时间增加。因此，在设计查询时，应仔细评估是否真的需要使用CASE语句，并考虑其他可能的优化方法。

2. 可读性和维护性

复杂的CASE语句可能会降低代码的可读性和维护性。为了确保代码易于理解和维护，建议将复杂的逻辑拆分成更小的部分，并使用有意义的变量名和注释来解释每个部分的功能。

总结

CASE语句是MySQL中功能强大的条件表达式工具，它可以在SELECT、UPDATE、DELETE等语句中灵活应用，帮助我们根据条件返回不同的值或执行不同的操作。通过合理使用CASE语句，我们可以构建出更加灵活和强大的SQL查询逻辑。然而，在使用CASE语句时，我们也需要注意性能、可读性和NULL处理等方面的问题，以确保代码的高效性和可维护性。

👨‍💻博主Python老吕说：如果您觉得本文有帮助，辛苦您🙏帮忙点赞、收藏、评论，您的举手之劳将对我提供了无限的写作动力！🤞

🔥精品付费专栏:《跟老吕学Python编程》、《Python游戏开发实战讲解》、《Python Web开发实战》、《Python网络爬虫实战》、《Python APP开发实战》

🌐前端:《HTML》、《CSS》、《JavaScript》、《Vue》

💻后端:《C语言》、《C++语言》、《Java语言》、《R语言》、《Ruby语言》、《PHP语言》、《Go语言》、《C#语言》、《Swift语言》、《跟老吕学Python编程·附录资料》

💾数据库:《Oracle》、《MYSQL》、《SQL》、《PostgreSQL》、《MongoDB》