sql 查询（case when）

1、 查询

`SELECT 
CASE WHEN STATUS='1' THEN '状态1'
WHEN STATUS='2' THEN '状态2'
WHEN STATUS='3' THEN '状态3'
WHEN STATUS='0' THEN '状态4'
ELSE '状态5' END AS '状态' 
FROM tb__info_xxx;

2、假设我们有一个包含学生成绩的表 students_scores，我们想要根据分数段给学生评级：

SELECT 
  student_name,
  score,
  CASE 
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 75 THEN '良好'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade
FROM students_scores;

3、ELSE 子句在 CASE WHEN 语句中用于指定默认结果，当所有条件都不满足时，返回 ELSE 后的值。

SELECT 
  product_name,
  price,
  CASE 
    WHEN price > 100 THEN '高端产品'
    ELSE '普通产品'
  END AS product_type
FROM products;

4、CASE WHEN 的嵌套使用

SELECT 
  employee_name,
  CASE 
    WHEN department = '销售' THEN 
      CASE 
        WHEN sales > 10000 THEN '顶级销售员'
        ELSE '普通销售员'
      END
    ELSE '非销售部门'
  END AS employee_category
FROM employees;

5、在 SELECT 中使用 CASE WHEN

SELECT 
  order_id,
  order_date,
  CASE 
    WHEN order_date < '2024-01-01' THEN '旧订单'
    ELSE '新订单'
  END AS order_type
FROM orders;

6、在 WHERE 中使用 CASE WHEN

SELECT 
  product_name,
  price
FROM products
WHERE 
  CASE 
    WHEN category = '电子产品' THEN price > 500
    ELSE price > 100
  END;

7、在 ORDER BY 中使用 CASE WHEN

SELECT 
  employee_name,
  department,
  salary
FROM employees
ORDER BY 
  CASE 
    WHEN department = '人事' THEN salary
    ELSE salary DESC
  END;

8、在 GROUP BY 和 HAVING 中使用 CASE WHEN

SELECT 
  department,
  COUNT(*),
  CASE 
    WHEN AVG(salary) > 5000 THEN '高薪部门'
    ELSE '普通部门'
  END AS department_type
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

9、实现多条件的复杂查询

SELECT 
  order_id,
  customer_name,
  CASE 
    WHEN order_amount >= 10000 AND order_date >= '2024-01-01' THEN '大单'
    WHEN order_amount < 10000 AND order_date >= '2024-01-01' THEN '普通单'
    ELSE '历史订单'
  END AS order_category
FROM orders;

10、数据清洗与转换

SELECT 
  customer_name,
  phone_number,
  CASE 
    WHEN phone_number LIKE '1%' THEN CONCAT('+86 ', phone_number)
    ELSE phone_number
  END AS formatted_phone_number
FROM customers;

11、统计与聚合操作中的应用

SELECT 
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = '已完成' THEN 1 ELSE 0 END) AS completed_orders,
  SUM(CASE WHEN status = '未完成' THEN 1 ELSE 0 END) AS pending_orders
FROM orders;