条件表达式：CASE WHEN与IF

-- 语法
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

-- 示例：订单状态转换
SELECT order_id,
       CASE status
           WHEN 1 THEN '待支付'
           WHEN 2 THEN '已支付'
           WHEN 3 THEN '已发货'
           WHEN 4 THEN '已完成'
           ELSE '未知'
       END AS status_text
FROM orders;

-- 语法
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

-- 示例：价格分级
SELECT name, price,
       CASE
           WHEN price < 2000 THEN '低价'
           WHEN price BETWEEN 2000 AND 5000 THEN '中价'
           WHEN price > 5000 THEN '高价'
           ELSE '未知'
       END AS price_level
FROM products;

SELECT name,
       CASE
           WHEN stock = 0 THEN '缺货'
           WHEN stock > 0 THEN
               CASE
                   WHEN stock < 10 THEN '库存紧张'
                   WHEN stock < 50 THEN '库存正常'
                   ELSE '库存充足'
               END
       END AS stock_status
FROM products;

-- IF(condition, value_if_true, value_if_false)
SELECT name, price,
       IF(price > 5000, '高价', '普通价') AS price_tag
FROM products;

-- 多条件判断（嵌套IF）
SELECT name,
       IF(stock > 0,
          IF(stock > 50, '充足', '正常'),
          '缺货'
       ) AS stock_status
FROM products;

-- IF：适合简单的二元判断
SELECT IF(score >= 60, '及格', '不及格') FROM students;

-- CASE WHEN：适合多条件判断
SELECT
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

-- IFNULL(expr, default_value)
SELECT name,
       IFNULL(phone, '未提供') AS phone
FROM users;

-- 计算时避免NULL
SELECT name,
       price * IFNULL(discount, 1) AS final_price
FROM products;

-- COALESCE(value1, value2, value3, ...)
SELECT name,
       COALESCE(mobile, phone, email, '无联系方式') AS contact
FROM users;

-- NULLIF(expr1, expr2)：如果相等返回NULL，否则返回expr1
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

-- 避免除零错误
SELECT sales / NULLIF(days, 0) AS avg_daily_sales
FROM stats;

-- 统计各价格区间的商品数
SELECT
    SUM(CASE WHEN price < 2000 THEN 1 ELSE 0 END) AS low_price_count,
    SUM(CASE WHEN price BETWEEN 2000 AND 5000 THEN 1 ELSE 0 END) AS mid_price_count,
    SUM(CASE WHEN price > 5000 THEN 1 ELSE 0 END) AS high_price_count
FROM products;

-- 按月统计各状态订单数
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- 根据参数动态排序
SET @sort_field = 'price';

SELECT * FROM products
ORDER BY
    CASE @sort_field
        WHEN 'price' THEN price
        WHEN 'sales' THEN sales
        WHEN 'stock' THEN stock
    END DESC;

-- 清洗性别数据
UPDATE users
SET gender = CASE
    WHEN gender IN ('male', 'M', '男', '1') THEN '男'
    WHEN gender IN ('female', 'F', '女', '0') THEN '女'
    ELSE '未知'
END;

-- 将多行转为多列
SELECT user_id,
       MAX(CASE WHEN subject = '数学' THEN score END) AS math_score,
       MAX(CASE WHEN subject = '语文' THEN score END) AS chinese_score,
       MAX(CASE WHEN subject = '英语' THEN score END) AS english_score
FROM scores
GROUP BY user_id;

-- ❌ 差：在索引列上使用CASE，索引失效
SELECT * FROM products
WHERE CASE WHEN category = '手机' THEN price > 3000 ELSE price > 5000 END;

-- ✅ 好：拆分条件
SELECT * FROM products
WHERE (category = '手机' AND price > 3000)
   OR (category != '手机' AND price > 5000);

-- ❌ 差：CASE重复计算
SELECT
    CASE WHEN price * 0.8 > 5000 THEN '高价' ELSE '普通' END AS tag1,
    CASE WHEN price * 0.8 > 3000 THEN '贵' ELSE '便宜' END AS tag2
FROM products;

-- ✅ 好：先计算再判断
SELECT
    CASE WHEN discount_price > 5000 THEN '高价' ELSE '普通' END AS tag1,
    CASE WHEN discount_price > 3000 THEN '贵' ELSE '便宜' END AS tag2
FROM (
    SELECT *, price * 0.8 AS discount_price FROM products
) t;