SQL 中 CASE WHEN 及 SELECT CASE WHEN 的用法详解

SQL中的CASE表达式是一个强大的条件逻辑工具，可以用于SELECT、WHERE、ORDER BY、GROUP BY等子句中。以下是详细用法说明：

1. 基本语法结构

简单CASE表达式

CASE 列名或表达式
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ...
    ELSE 默认结果
END

搜索CASE表达式（更常用）

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ...
    ELSE 默认结果
END

2. 在SELECT中的基本用法

示例1：简单分类

SELECT 
    employee_name,
    salary,
    CASE
        WHEN salary > 10000 THEN '高薪'
        WHEN salary > 5000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

示例2：数值转换

SELECT 
    product_name,
    CASE status
        WHEN 'A' THEN '激活'
        WHEN 'I' THEN '禁用'
        WHEN 'P' THEN '待审核'
        ELSE '未知'
    END AS status_desc
FROM products;

3. 高级用法

嵌套CASE

SELECT 
    student_name,
    score,
    CASE
        WHEN score >= 90 THEN 
            CASE 
                WHEN score = 100 THEN '完美'
                ELSE '优秀'
            END
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

在聚合函数中使用

-- 统计不同等级人数
SELECT 
    COUNT(CASE WHEN score >= 90 THEN 1 END) AS excellent_count,
    COUNT(CASE WHEN score >= 60 AND score < 90 THEN 1 END) AS pass_count,
    COUNT(CASE WHEN score < 60 THEN 1 END) AS fail_count
FROM students;

4. 在WHERE子句中使用

-- 查找特定条件下的数据
SELECT *
FROM orders
WHERE 
    CASE 
        WHEN customer_type = 'VIP' THEN total_amount > 1000
        WHEN customer_type = 'NORMAL' THEN total_amount > 5000
        ELSE total_amount > 100
    END;

5. 在ORDER BY中使用

-- 自定义排序规则
SELECT 
    product_name,
    category,
    price
FROM products
ORDER BY 
    CASE category
        WHEN '电子' THEN 1
        WHEN '服装' THEN 2
        WHEN '食品' THEN 3
        ELSE 4
    END,
    price DESC;

6. 在UPDATE语句中使用

UPDATE employees
SET bonus = 
    CASE
        WHEN performance_rating = 'A' THEN salary * 0.2
        WHEN performance_rating = 'B' THEN salary * 0.1
        ELSE salary * 0.05
    END;

7. 在GROUP BY中使用

-- 按年龄段分组统计
SELECT 
    CASE
        WHEN age < 20 THEN '20岁以下'
        WHEN age < 30 THEN '20-29岁'
        WHEN age < 40 THEN '30-39岁'
        ELSE '40岁以上'
    END AS age_group,
    COUNT(*) AS user_count,
    AVG(income) AS avg_income
FROM users
GROUP BY 
    CASE
        WHEN age < 20 THEN '20岁以下'
        WHEN age < 30 THEN '20-29岁'
        WHEN age < 40 THEN '30-39岁'
        ELSE '40岁以上'
    END;

8. 多条件组合示例

-- 综合示例：客户分类
SELECT 
    customer_id,
    customer_name,
    total_purchases,
    last_purchase_date,
    CASE
        WHEN total_purchases > 100000 AND DATEDIFF(day, last_purchase_date, GETDATE()) < 30 
            THEN 'VIP活跃客户'
        WHEN total_purchases > 50000 AND DATEDIFF(month, last_purchase_date, GETDATE()) < 3 
            THEN '重要客户'
        WHEN total_purchases > 10000 AND DATEDIFF(month, last_purchase_date, GETDATE()) < 6 
            THEN '一般客户'
        WHEN DATEDIFF(year, last_purchase_date, GETDATE()) > 1 
            THEN '流失客户'
        ELSE '普通客户'
    END AS customer_category
FROM customers;