SQL条件函数

# SQL条件函数

作者：哪吒
时间：2024年12月

# 概述

SQL条件函数用于根据特定条件返回不同的值。这些函数在数据处理、报表生成和业务逻辑实现中非常有用，能够让我们在SQL查询中实现复杂的条件判断和数据转换。

# CASE表达式

# 简单CASE表达式

根据表达式的值进行匹配。

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

-- 示例：根据成绩等级显示描述
SELECT 
    student_name,
    score,
    CASE score
        WHEN 'A' THEN '优秀'
        WHEN 'B' THEN '良好'
        WHEN 'C' THEN '及格'
        WHEN 'D' THEN '不及格'
        ELSE '未知等级'
    END AS grade_description
FROM students;

# 搜索CASE表达式

根据条件表达式进行判断。

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

-- 示例：根据分数范围划分等级
SELECT 
    student_name,
    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 students;

-- 复杂条件示例
SELECT 
    product_name,
    price,
    stock_quantity,
    CASE 
        WHEN stock_quantity = 0 THEN '缺货'
        WHEN stock_quantity < 10 THEN '库存不足'
        WHEN stock_quantity < 50 THEN '库存正常'
        ELSE '库存充足'
    END AS stock_status,
    CASE 
        WHEN price < 100 THEN '低价商品'
        WHEN price BETWEEN 100 AND 500 THEN '中价商品'
        WHEN price > 500 THEN '高价商品'
        ELSE '价格未知'
    END AS price_category
FROM products;

# IF函数

# IF() - 简单条件判断

MySQL特有的条件函数。

-- 基本语法
IF(condition, value_if_true, value_if_false)

-- 示例
SELECT 
    student_name,
    score,
    IF(score >= 60, '及格', '不及格') AS pass_status,
    IF(score >= 90, '奖学金', '无奖学金') AS scholarship_status
FROM students;

-- 嵌套IF
SELECT 
    employee_name,
    salary,
    IF(salary > 10000, 
        '高薪', 
        IF(salary > 5000, '中薪', '低薪')
    ) AS salary_level
FROM employees;

# IFNULL和ISNULL函数

# IFNULL() - 处理NULL值

如果第一个参数为NULL，返回第二个参数。

-- 基本语法
IFNULL(expression, alternative_value)

-- 示例
SELECT 
    customer_name,
    IFNULL(phone, '未提供电话') AS contact_phone,
    IFNULL(email, '未提供邮箱') AS contact_email,
    IFNULL(address, '地址未知') AS customer_address
FROM customers;

-- 数值计算中的NULL处理
SELECT 
    product_name,
    price,
    discount,
    price * IFNULL(discount, 1) AS final_price
FROM products;

# ISNULL() - 检查NULL值

检查表达式是否为NULL。

-- 基本语法（SQL Server）
ISNULL(expression, replacement_value)

-- MySQL中使用IS NULL
SELECT 
    customer_name,
    phone IS NULL AS phone_missing,
    email IS NULL AS email_missing
FROM customers;

# COALESCE函数

# COALESCE() - 返回第一个非NULL值

从左到右检查参数，返回第一个非NULL值。

-- 基本语法
COALESCE(value1, value2, value3, ...)

-- 示例：联系方式优先级
SELECT 
    customer_name,
    COALESCE(mobile_phone, home_phone, work_phone, '无联系方式') AS primary_contact,
    COALESCE(email, backup_email, '无邮箱') AS primary_email
FROM customers;

-- 数据合并示例
SELECT 
    product_name,
    COALESCE(sale_price, list_price, cost_price * 1.5) AS display_price,
    COALESCE(description, short_description, product_name) AS product_info
FROM products;

# NULLIF函数

# NULLIF() - 条件返回NULL

如果两个参数相等，返回NULL，否则返回第一个参数。

-- 基本语法
NULLIF(expression1, expression2)

-- 示例：避免除零错误
SELECT 
    product_name,
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM product_statistics;

-- 数据清理示例
SELECT 
    customer_name,
    NULLIF(phone, '') AS clean_phone,  -- 空字符串转为NULL
    NULLIF(email, 'N/A') AS clean_email  -- 'N/A'转为NULL
FROM customers;

# GREATEST和LEAST函数

# GREATEST() - 返回最大值

返回参数列表中的最大值。

-- 基本语法
GREATEST(value1, value2, value3, ...)

-- 示例
SELECT 
    student_name,
    math_score,
    english_score,
    science_score,
    GREATEST(math_score, english_score, science_score) AS highest_score
FROM student_scores;

-- 日期比较
SELECT 
    order_id,
    order_date,
    ship_date,
    delivery_date,
    GREATEST(order_date, ship_date, delivery_date) AS latest_date
FROM orders;

# LEAST() - 返回最小值

返回参数列表中的最小值。

-- 基本语法
LEAST(value1, value2, value3, ...)

-- 示例
SELECT 
    product_name,
    supplier1_price,
    supplier2_price,
    supplier3_price,
    LEAST(supplier1_price, supplier2_price, supplier3_price) AS best_price
FROM product_prices;

-- 时间比较
SELECT 
    task_name,
    estimated_hours,
    actual_hours,
    LEAST(estimated_hours, actual_hours) AS minimum_hours
FROM project_tasks;

# 实战示例

# 示例1：员工薪资等级分析

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    performance_score INT
);

INSERT INTO employees VALUES
(1, '张三', '技术部', 8000.00, 2000.00, 85),
(2, '李四', '销售部', 6000.00, NULL, 92),
(3, '王五', '技术部', 12000.00, 3000.00, 78),
(4, '赵六', '人事部', 5500.00, 1000.00, NULL),
(5, '钱七', '销售部', 7500.00, 2500.00, 88);

-- 综合薪资分析
SELECT 
    name,
    department,
    salary,
    IFNULL(bonus, 0) AS bonus,
    salary + IFNULL(bonus, 0) AS total_compensation,
    
    -- 薪资等级
    CASE 
        WHEN salary >= 10000 THEN '高级'
        WHEN salary >= 7000 THEN '中级'
        WHEN salary >= 5000 THEN '初级'
        ELSE '实习'
    END AS salary_grade,
    
    -- 绩效等级
    CASE 
        WHEN performance_score IS NULL THEN '未评估'
        WHEN performance_score >= 90 THEN '优秀'
        WHEN performance_score >= 80 THEN '良好'
        WHEN performance_score >= 70 THEN '合格'
        ELSE '需改进'
    END AS performance_grade,
    
    -- 奖金状态
    IF(bonus IS NULL OR bonus = 0, '无奖金', '有奖金') AS bonus_status,
    
    -- 综合评价
    CASE 
        WHEN salary >= 10000 AND COALESCE(performance_score, 0) >= 85 THEN '核心员工'
        WHEN salary >= 7000 AND COALESCE(performance_score, 0) >= 80 THEN '骨干员工'
        WHEN COALESCE(performance_score, 0) >= 90 THEN '潜力员工'
        ELSE '普通员工'
    END AS employee_category
FROM employees
ORDER BY total_compensation DESC;

# 示例2：订单状态管理

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    ship_date DATE,
    delivery_date DATE,
    order_amount DECIMAL(10,2),
    payment_status VARCHAR(20),
    shipping_status VARCHAR(20)
);

INSERT INTO orders VALUES
(1, 1001, '2024-12-20', '2024-12-21', '2024-12-23', 299.99, 'paid', 'delivered'),
(2, 1002, '2024-12-22', '2024-12-23', NULL, 159.50, 'paid', 'shipped'),
(3, 1003, '2024-12-23', NULL, NULL, 89.99, 'pending', 'processing'),
(4, 1004, '2024-12-24', NULL, NULL, 449.00, 'paid', 'processing');

-- 订单状态分析
SELECT 
    order_id,
    customer_id,
    order_date,
    order_amount,
    
    -- 订单状态
    CASE 
        WHEN delivery_date IS NOT NULL THEN '已送达'
        WHEN ship_date IS NOT NULL THEN '已发货'
        WHEN payment_status = 'paid' THEN '已付款待发货'
        WHEN payment_status = 'pending' THEN '待付款'
        ELSE '状态未知'
    END AS order_status,
    
    -- 处理时效
    CASE 
        WHEN ship_date IS NULL THEN NULL
        ELSE DATEDIFF(ship_date, order_date)
    END AS processing_days,
    
    CASE 
        WHEN delivery_date IS NULL THEN NULL
        ELSE DATEDIFF(delivery_date, COALESCE(ship_date, order_date))
    END AS shipping_days,
    
    -- 总耗时
    COALESCE(
        DATEDIFF(delivery_date, order_date),
        DATEDIFF(CURDATE(), order_date)
    ) AS total_days,
    
    -- 时效评价
    CASE 
        WHEN delivery_date IS NULL THEN '进行中'
        WHEN DATEDIFF(delivery_date, order_date) <= 3 THEN '快速'
        WHEN DATEDIFF(delivery_date, order_date) <= 7 THEN '正常'
        ELSE '较慢'
    END AS delivery_speed,
    
    -- 订单金额等级
    CASE 
        WHEN order_amount >= 300 THEN '大额订单'
        WHEN order_amount >= 100 THEN '中额订单'
        ELSE '小额订单'
    END AS order_category,
    
    -- 优先级
    CASE 
        WHEN order_amount >= 300 AND payment_status = 'paid' THEN '高优先级'
        WHEN payment_status = 'paid' THEN '中优先级'
        ELSE '低优先级'
    END AS priority
FROM orders
ORDER BY 
    CASE 
        WHEN order_amount >= 300 AND payment_status = 'paid' THEN 1
        WHEN payment_status = 'paid' THEN 2
        ELSE 3
    END,
    order_date DESC;

# 示例3：学生成绩分析

-- 创建成绩表
CREATE TABLE student_grades (
    student_id INT,
    student_name VARCHAR(50),
    subject VARCHAR(30),
    score INT,
    exam_date DATE
);

INSERT INTO student_grades VALUES
(1, '张三', '数学', 85, '2024-12-15'),
(1, '张三', '英语', 92, '2024-12-16'),
(1, '张三', '物理', 78, '2024-12-17'),
(2, '李四', '数学', 76, '2024-12-15'),
(2, '李四', '英语', 88, '2024-12-16'),
(2, '李四', '物理', NULL, '2024-12-17'),
(3, '王五', '数学', 95, '2024-12-15'),
(3, '王五', '英语', 89, '2024-12-16'),
(3, '王五', '物理', 91, '2024-12-17');

-- 成绩分析报告
SELECT 
    student_id,
    student_name,
    
    -- 各科成绩
    MAX(CASE WHEN subject = '数学' THEN score END) AS math_score,
    MAX(CASE WHEN subject = '英语' THEN score END) AS english_score,
    MAX(CASE WHEN subject = '物理' THEN score END) AS physics_score,
    
    -- 平均分（忽略NULL值）
    ROUND(AVG(NULLIF(score, 0)), 2) AS average_score,
    
    -- 最高分和最低分
    MAX(score) AS highest_score,
    MIN(score) AS lowest_score,
    
    -- 成绩等级
    CASE 
        WHEN AVG(NULLIF(score, 0)) >= 90 THEN 'A'
        WHEN AVG(NULLIF(score, 0)) >= 80 THEN 'B'
        WHEN AVG(NULLIF(score, 0)) >= 70 THEN 'C'
        WHEN AVG(NULLIF(score, 0)) >= 60 THEN 'D'
        ELSE 'F'
    END AS overall_grade,
    
    -- 奖学金评定
    CASE 
        WHEN AVG(NULLIF(score, 0)) >= 90 AND MIN(score) >= 80 THEN '一等奖学金'
        WHEN AVG(NULLIF(score, 0)) >= 85 AND MIN(score) >= 75 THEN '二等奖学金'
        WHEN AVG(NULLIF(score, 0)) >= 80 AND MIN(score) >= 70 THEN '三等奖学金'
        ELSE '无奖学金'
    END AS scholarship,
    
    -- 学习建议
    CASE 
        WHEN MIN(score) < 60 THEN '需要重点补习薄弱科目'
        WHEN AVG(NULLIF(score, 0)) < 75 THEN '需要提高整体成绩'
        WHEN MAX(score) - MIN(score) > 20 THEN '需要平衡各科发展'
        ELSE '继续保持'
    END AS study_advice
FROM student_grades
GROUP BY student_id, student_name
ORDER BY average_score DESC;

# 性能优化建议

# 1. 避免复杂嵌套

-- 不推荐：过度嵌套的CASE
SELECT 
    CASE 
        WHEN condition1 THEN 
            CASE 
                WHEN condition2 THEN 'result1'
                ELSE 'result2'
            END
        ELSE 'result3'
    END
FROM table_name;

-- 推荐：简化逻辑
SELECT 
    CASE 
        WHEN condition1 AND condition2 THEN 'result1'
        WHEN condition1 THEN 'result2'
        ELSE 'result3'
    END
FROM table_name;

# 2. 使用索引优化条件判断

-- 为经常用于条件判断的字段创建索引
CREATE INDEX idx_status ON orders(payment_status, shipping_status);
CREATE INDEX idx_score ON students(score);

# 3. 避免在WHERE子句中使用复杂条件函数

-- 不推荐：在WHERE中使用复杂CASE
SELECT * FROM orders 
WHERE CASE 
    WHEN payment_status = 'paid' THEN 1
    ELSE 0
END = 1;

-- 推荐：直接使用条件
SELECT * FROM orders WHERE payment_status = 'paid';

# 常见错误和解决方案

# 1. NULL值处理错误

-- 错误：忽略NULL值
SELECT AVG(score) FROM students;  -- NULL值会被忽略

-- 正确：明确处理NULL值
SELECT AVG(COALESCE(score, 0)) FROM students;  -- 将NULL视为0
SELECT AVG(score) FROM students WHERE score IS NOT NULL;  -- 排除NULL

# 2. 数据类型不匹配

-- 错误：类型不匹配
SELECT CASE WHEN score > 80 THEN 'good' ELSE 0 END FROM students;

-- 正确：保持类型一致
SELECT CASE WHEN score > 80 THEN 'good' ELSE 'poor' END FROM students;
SELECT CASE WHEN score > 80 THEN 1 ELSE 0 END FROM students;

# 3. 条件顺序错误

-- 错误：条件顺序不当
CASE 
    WHEN score >= 60 THEN 'pass'
    WHEN score >= 80 THEN 'good'  -- 永远不会执行
    WHEN score >= 90 THEN 'excellent'  -- 永远不会执行
END

-- 正确：从高到低排序
CASE 
    WHEN score >= 90 THEN 'excellent'
    WHEN score >= 80 THEN 'good'
    WHEN score >= 60 THEN 'pass'
    ELSE 'fail'
END

# 最佳实践

合理使用ELSE子句：总是提供ELSE子句以处理未预期的情况
保持数据类型一致：CASE表达式的所有分支应返回相同类型的值
优化条件顺序：将最可能匹配的条件放在前面
使用适当的NULL处理函数：根据业务需求选择IFNULL、COALESCE或NULLIF
避免过度复杂：复杂的条件逻辑考虑在应用层处理

# 总结

SQL条件函数是处理业务逻辑的重要工具，合理使用可以：

在数据库层面实现复杂的条件判断
简化应用程序的数据处理逻辑
提高数据查询的灵活性
实现动态的数据转换和格式化

掌握这些函数的使用技巧，能够让你的SQL查询更加强大和灵活。

日期时间函数窗口函数

Jeskson-微服务分布式

Choose mode