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 '普通销售员'ENDELSE '非销售部门'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 > 500ELSE price > 100END;

7、在 ORDER BY 中使用 CASE WHEN

SELECT employee_name,department,salary
FROM employees
ORDER BY CASE WHEN department = '人事' THEN salaryELSE salary DESCEND;

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_numberEND 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;

12、case when 截取字符串

SELECTSFZHM,XM,CASE WHEN SUBSTR(SFZHM, 0, 6) = 'XXX701' THEN 'XXX701' WHEN SUBSTR(SFZHM, 0, 6) = 'XXX727' THEN 'XXX727'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX726' THEN 'XXX726'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX722' THEN 'XXX722'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX723' THEN 'XXX723'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX725' THEN 'XXX725'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX724' THEN 'XXX724'WHEN SUBSTR(SFZHM, 0, 6) = 'XXX802' THEN 'XXX802'ELSE 'QT_9'
END AS SHORT_SFZ
FROM SYS_USER1 WHERE 1=1 group by SFZHM,XM;