MySQL数据库中如何巧妙运用COALESCE函数与UNION、CASE相结合进行数据整合与分析？

未希 • 2024-10-05 01:57 • 代码编程 • 阅读 4

MySQL数据库中使用COALESCE函数、UNION和CASE的相关构造示例
1. COALESCE函数
COALESCE函数返回列表中第一个非NULL值。
示例：假设有一个订单表orders，包含字段order_id和amount，其中amount可能为NULL。
我们想要返回每个订单的金额，如果金额为NULL，则返回默认值0。
SELECT COALESCE(amount, 0) AS total_amount
FROM orders;
2. UNION操作符
UNION操作符用于合并两个或多个SELECT语句的结果集，并去除重复的行。
示例：有两个表employees和contractors，都包含字段name和salary。
我们想要查看所有员工的姓名和薪水，使用UNION去除重复的姓名。
SELECT name, salary
FROM employees
UNION
SELECT name, salary
FROM contractors;
3. CASE语句
CASE语句用于根据条件返回不同的值。
示例：假设有一个成绩表grades，包含字段student_id和score。
我们想要根据分数返回不同的等级。
SELECT student_id,
       score,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           WHEN score >= 70 THEN 'C'
           WHEN score >= 60 THEN 'D'
           ELSE 'F'
       END AS grade
FROM grades;
结合COALESCE、UNION和CASE的示例
假设我们有两个表：sales 和 returns，都包含字段 product_id 和 amount。
sales 表记录销售金额，returns 表记录退货金额。
我们想要计算每个产品的净销售额（销售金额减去退货金额），如果退货金额为NULL，则默认为0。
SELECT product_id,
       COALESCE(SUM(s.amount), 0) COALESCE(SUM(r.amount), 0) AS net_sales
FROM sales s
LEFT JOIN returns r ON s.product_id = r.product_id
GROUP BY product_id
UNION ALL
SELECT product_id,
       COALESCE(SUM(s.amount), 0) COALESCE(SUM(r.amount), 0)
FROM sales s
LEFT JOIN returns r ON s.product_id = r.product_id
WHERE r.product_id IS NULL
GROUP BY product_id;

SQL代码块展示了如何在MySQL中使用COALESCE函数、UNION操作符和CASE语句，并给出了具体的示例来解释这些构造的用法。