详细介绍：第五章:MySQL DQL 进阶 —— 动态计算与分类（IF 与 CASE WHEN）多表查询

条件判断 —— 动态计算与分类（IF 与 CASE WHEN）

核心价值：根据字段值动态返回不同结果，实现“如果…就…”的逻辑。

一、`IF()` 函数（MySQL 简洁写法）

语法：

IF(condition, value_if_true, value_if_false)

示例 1：用户状态标记

SELECT
    username,
    created_at,
    IF(created_at > '2025-01-01', '新用户', '老用户') AS user_type
FROM users;

✅ 输出：

username	created_at	user_type
alice	2025-11-01 10:00:00	新用户
bob	2024-05-10 09:00:00	老用户

示例 2：订单是否大额

SELECT
    product_name,
    amount,
    IF(amount >= 1000, '高价值', '普通') AS value_level
FROM orders;

⚠️ 注意：IF() 只支持 二选一（类似三元运算符）。多条件请用 CASE。

二、`CASE WHEN` 语句（SQL 标准，推荐用于复杂逻辑）

有两种写法：

1. 简单 `CASE`（等值判断）

CASE column
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

SELECT
    product_name,
    status,
    CASE status
        WHEN 'pending'   THEN '待支付'
        WHEN 'paid'      THEN '已支付'
        WHEN 'shipped'   THEN '已发货'
        WHEN 'cancelled' THEN '已取消'
        ELSE '未知状态'
    END AS status_zh
FROM orders;

2. 搜索 `CASE`（条件判断，更常用）

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

SELECT
    username,
    created_at,
    CASE
        WHEN created_at > '2025-10-01' THEN '活跃用户'
        WHEN created_at > '2025-01-01' THEN '普通用户'
        WHEN created_at IS NOT NULL THEN '沉默用户'
        ELSE '异常用户'
    END AS user_category
FROM users;

三、在聚合中使用条件判断（强大组合！）

统计不同类型订单数量

SELECT
    COUNT(*) AS total_orders,
    SUM(IF(status = 'paid', 1, 0)) AS paid_count,
    SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END) AS high_value_count
FROM orders;

技巧：SUM(IF(...)) 或 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 是实现“条件计数”的经典模式。

按用户分组 + 分类统计

SELECT
    user_id,
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    AVG(CASE WHEN status = 'paid' THEN amount END) AS avg_paid_amount
FROM orders
GROUP BY user_id;

注意：AVG(CASE WHEN ... THEN amount END) 会自动忽略 NULL，只对符合条件的行求平均。

四、`IF` vs `CASE WHEN` 对比

特性	`IF()`	`CASE WHEN`
标准兼容性	❌ MySQL 特有	✅ ANSI SQL 标准
条件数量	仅支持 1 个条件（二选一）	支持任意多个条件
可读性	简单场景更简洁	复杂逻辑更清晰
嵌套能力	可嵌套，但难维护	可嵌套，结构清晰
适用场景	快速二值判断	多分支、等值映射、复杂逻辑

✅ 最佳实践：
简单二选一 → 用 IF()
多条件、跨数据库兼容 → 用 CASE WHEN

五、常见错误与避坑

❌ 错误 1：忘记 `END`

-- 缺少 END，语法错误！
SELECT CASE WHEN amount > 1000 THEN 'high' FROM orders;

✅ 正确：

SELECT CASE WHEN amount > 1000 THEN 'high' END FROM orders;

❌ 错误 2：在 `WHERE` 中直接用别名（即使来自 CASE）

SELECT
    username,
    CASE WHEN created_at > '2025-01-01' THEN 'new' ELSE 'old' END AS type
FROM users
WHERE type = 'new';  -- ❌ Unknown column 'type'

✅ 正确做法：

-- 方法1：重复条件
WHERE created_at > '2025-01-01'
-- 方法2：用子查询
SELECT * FROM (
    SELECT username,
           CASE WHEN created_at > '2025-01-01' THEN 'new' ELSE 'old' END AS type
    FROM users
) t WHERE type = 'new';

六、小结：条件判断能力清单

场景	推荐写法
二值判断（是/否）	`IF(condition, A, B)`
多分支逻辑	`CASE WHEN ... THEN ... END`
状态码转中文	`CASE status WHEN 'code' THEN '文本' END`
条件计数/求和	`SUM(CASE WHEN ... THEN 1 ELSE 0 END)`
跨数据库兼容	优先用 `CASE`

一句话总结：
“简单用 IF，复杂用 CASE；聚合加条件，统计更灵活。”

二、环境准备（基于第三章的数据库）

我们沿用 demo_dml 数据库，并新增两张表：

-- 订单表
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB CHARSET=utf8mb4;
-- 插入测试订单
INSERT INTO orders (user_id, product_name, amount, status) VALUES
(1, '笔记本电脑', 5999.00, 'paid'),
(1, '无线鼠标', 89.00, 'paid'),
(2, '机械键盘', 399.00, 'shipped'),
(3, '显示器', 1299.00, 'paid');

当前数据关系：

users (id=1: Alice) → orders (2笔)
users (id=2: Bob)   → orders (1笔)
users (id=3: Charlie)→ orders (1笔)

三、多表连接（JOIN）—— 关联数据的基石

1. `INNER JOIN`：只返回匹配的记录

-- 查询已支付订单的用户信息
SELECT
    u.username,
    o.product_name,
    o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

✅ 结果：Alice 的两笔 + Charlie 的一笔（Bob 的订单状态是 shipped，不满足条件）。

2. `LEFT JOIN`：保留左表所有记录（即使无匹配）

-- 查询所有用户及其订单（包括无订单用户）
SELECT
    u.username,
    COALESCE(o.product_name, '无订单') AS product,
    COALESCE(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

✅ 结果：Charlie、Bob、Alice 都会出现，即使某人没订单。

关键技巧：用 COALESCE() 处理 NULL，避免前端显示异常。

3. `RIGHT JOIN` 与 `FULL OUTER JOIN`

MySQL 不支持 FULL OUTER JOIN（可用 LEFT JOIN + RIGHT JOIN + UNION 模拟）；
RIGHT JOIN 很少用，通常改写为 LEFT JOIN 更清晰。

四、子查询（Subquery）—— 查询中的查询

1. 单行单列子查询（Scalar Subquery）

返回 一行一列 的结果（即一个单一值）。
常用于 WHERE、SELECT、HAVING 等子句中。
可以与比较运算符（如 =, >, <, <> 等）一起使用。

✅ 示例：

SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

这里的 (SELECT MAX(salary) FROM employees) 就是一个单行单列子查询，返回最高薪资这个单一值。

2. 单行多列子查询

返回 一行多列 的结果。
通常用于比较多个字段是否匹配某一行的多个值。
需要使用括号将多个列包裹起来进行比较。

✅ 示例：

SELECT *
FROM orders
WHERE (customer_id, order_date) = (
    SELECT customer_id, MAX(order_date)
    FROM orders
    GROUP BY customer_id
    LIMIT 1
);

注意：这种用法在某些数据库（如 MySQL）中支持，但在其他数据库中可能有限制。

3. 多行单列子查询

返回 多行一列 的结果。
通常配合 多值操作符 使用，如 IN、ANY、ALL、EXISTS 等。
不能直接使用 = 比较（因为会报错：“子查询返回了多于一行”）。

✅ 示例（使用 IN）：

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

✅ 示例（使用 ANY）：

SELECT *
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category = 'Electronics'
);

4. 多行多列子查询

返回 多行多列 的结果。
通常用于更复杂的逻辑，比如 EXISTS 或 JOIN 场景。
在 WHERE 中使用时需注意语法兼容性。

✅ 示例（使用 EXISTS）：

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

总结对比表：

类型	行数	列数	常用操作符/场景
单行单列	1	1	`=`, `>`, `<`, `SELECT` 表达式
单行多列	1	≥2	`(col1, col2) = (...)`
多行单列	≥2	1	`IN`, `ANY`, `ALL`
多行多列	≥2	≥2	`EXISTS`, `JOIN` 等

⚠️ 注意事项：

如果你写了一个本应是单行单列的子查询，但实际返回了多行，SQL 会报错（例如：Subquery returns more than one row）。
使用聚合函数（如 MAX(), MIN(), COUNT()）通常能保证返回单行单列。
在性能敏感的场景中，有时用 JOIN 替代子查询会更高效。

案例: 场景----找出消费总额超过平均值的用户

方法 1：`WHERE` 中的标量子查询

SELECT
    user_id,
    SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING total_spent > (
    SELECT AVG(total_per_user)
    FROM (
        SELECT SUM(amount) AS total_per_user
        FROM orders
        WHERE status = 'paid'
        GROUP BY user_id
    ) AS avg_table
);

方法 2：`FROM` 中的派生表（更高效）

SELECT
    t.user_id,
    u.username,
    t.total_spent
FROM (
    SELECT
        user_id,
        SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
) AS t
JOIN users u ON t.user_id = u.id
WHERE t.total_spent > (
    SELECT AVG(total_spent)
    FROM (
        SELECT SUM(amount) AS total_spent
        FROM orders
        WHERE status = 'paid'
        GROUP BY user_id
    ) AS avg_t
);

⚠️ 注意：子查询性能可能较差，大数据量建议用 JOIN 优化。

五、聚合分析（GROUP BY + 聚合函数）

常用聚合函数：

函数	说明
`COUNT()`	计数
`SUM()`	求和
`AVG()`	平均值
`MAX()` / `MIN()`	最大/最小值
`GROUP_CONCAT()`	合并字符串

实战：用户消费画像

SELECT
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent,
    AVG(o.amount) AS avg_order_value,
    GROUP_CONCAT(o.product_name ORDER BY o.created_at) AS products
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

✅ 输出：

username	order_count	total_spent	avg_order_value	products
alice	2	6088.00	3044.00	笔记本电脑,无线鼠标
charlie	1	1299.00	1299.00	显示器
bob	0	0	NULL	无

技巧：LEFT JOIN 中把过滤条件 status='paid' 放在 ON 子句，而非 WHERE，否则会过滤掉无订单用户！

六、窗口函数（MySQL 8.0+）—— 分析利器

⚠️ 要求 MySQL ≥ 8.0。若使用 5.7，请跳过此节或升级。

场景：给用户按消费排名

SELECT
    u.username,
    SUM(o.amount) AS total_spent,
    RANK() OVER (ORDER BY SUM(o.amount) DESC) AS spending_rank
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'
GROUP BY u.id, u.username;

✅ 结果：

username	total_spent	spending_rank
alice	6088.00	1
charlie	1299.00	2
bob	0	3

其他常用窗口函数：

ROW_NUMBER()：唯一行号
DENSE_RANK()：密集排名（无间隔）
LAG()/LEAD()：前后行数据引用
NTILE(n)：分桶（如 quartile）

四大类窗口函数详解

第一类：排名函数（Ranking Functions）

函数	说明	示例
`ROW_NUMBER()`	连续唯一排名（1,2,3…）	每组第1名
`RANK()`	跳跃排名（相同值并列，下一名跳过）	1,1,3…
`DENSE_RANK()`	密集排名（相同值并列，下一名连续）	1,1,2…
`NTILE(n)`	将分区分为 n 个桶	四分位、十分位

示例：用户订单排名

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS drnk
FROM orders;

假设用户1有两笔订单：5999 和 89
✅ 结果：

user_id	amount	rn	rnk	drnk
1	5999	1	1	1
1	89	2	2	2

若两笔都是 1000：

amount	rn	rnk	drnk
1000	1	1	1
1000	2	1	1

应用：Top N、去重（取 rn = 1）、分位分析。

第二类：聚合函数（作为窗口函数）

几乎所有聚合函数都可作窗口函数：

SUM(), AVG(), COUNT(), MAX(), MIN(), STDDEV(), VAR()

示例：累计销售额（Running Total）

SELECT
    order_id,
    created_at,
    amount,
    SUM(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders
ORDER BY created_at;

✅ 输出：

order_id	created_at	amount	running_total
1	2025-11-01 10:00	5999	5999
2	2025-11-02 11:00	89	6088
3	2025-11-03 09:00	399	6487

关键：ROWS UNBOUNDED PRECEDING 表示从第一行到当前行。

第三类：偏移函数（Offset / Navigation）

用于访问“前后行”的数据：

函数	说明
`LAG(col, n, default)`	向上取第 n 行的值（默认 n=1）
`LEAD(col, n, default)`	向下取第 n 行的值
`FIRST_VALUE(col)`	窗口第一行的值
`LAST_VALUE(col)`	窗口最后一行的值

示例：订单金额环比变化

SELECT
    order_id,
    created_at,
    amount,
    LAG(amount, 1) OVER (ORDER BY created_at) AS prev_amount,
    amount - LAG(amount, 1) OVER (ORDER BY created_at) AS diff
FROM orders
ORDER BY created_at;

✅ 输出：

order_id	amount	prev_amount	diff
1	5999	NULL	NULL
2	89	5999	-5910
3	399	89	310

应用：同比/环比、趋势分析、差值计算。

第四类：分布函数（Distribution）

函数	说明
`PERCENT_RANK()`	百分等级（0 到 1）
`CUME_DIST()`	累积分布
`NTILE(n)`	分桶（已在排名类介绍）

示例：用户消费百分位

SELECT
    user_id,
    total_spent,
    PERCENT_RANK() OVER (ORDER BY total_spent) AS pct_rank
FROM (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
) t;

`OVER()` 子句深度解析

1. `PARTITION BY`：分组（窗口边界）

类似 GROUP BY，但不合并行
每个分区内独立计算

-- 每个用户的订单排名
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC)

2. `ORDER BY`：窗口内排序

决定窗口函数的计算顺序
对 LAG/LEAD、累计和等至关重要

3. 帧范围（Frame Clause）：精确控制窗口行

语法：

ROWS BETWEEN start AND end
-- 或
RANGE BETWEEN start AND end

常用选项：

选项	含义
`UNBOUNDED PRECEDING`	从分区第一行开始
`CURRENT ROW`	当前行
`n PRECEDING`	当前行前 n 行
`n FOLLOWING`	当前行后 n 行
`UNBOUNDED FOLLOWING`	到分区最后一行

示例：3日移动平均

SELECT
    created_at,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3d
FROM orders;

⚠️ 注意：ROWS 基于物理行，RANGE 基于值范围（需谨慎使用）。

实战案例

案例1：每个用户的最新订单

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

案例2：销售额 Top 3 用户（允许并列）

SELECT user_id, total_spent
FROM (
    SELECT
        user_id,
        SUM(amount) AS total_spent,
        DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS drnk
    FROM orders
    GROUP BY user_id
) t
WHERE drnk <= 3;

案例3：订单金额高于用户平均值的记录

SELECT
    order_id,
    user_id,
    amount,
    avg_user_amount
FROM (
    SELECT
        *,
        AVG(amount) OVER (PARTITION BY user_id) AS avg_user_amount
    FROM orders
) t
WHERE amount > avg_user_amount;

常见误区与性能提示

❌ 误区1：认为窗口函数会改变行数

✅ 正解：行数不变，只是每行新增计算列。

❌ 误区2：在 `WHERE` 中使用窗口函数

-- ❌ 错误！窗口函数在 SELECT 阶段计算，WHERE 无法访问
SELECT * FROM orders WHERE ROW_NUMBER() OVER (...) = 1;

✅ 正确：用子查询或 CTE。

⚡ 性能提示：

窗口函数通常比自连接或子查询更快、更易读
确保 PARTITION BY 和 ORDER BY 字段有合适索引
避免在大表上无分区的全局窗口（如 OVER ()）

MySQL 版本要求

✅ 必须 MySQL ≥ 8.0
❌ MySQL 5.7 及以下 不支持窗口函数

可通过以下命令检查版本：

SELECT VERSION(); -- 需 >= 8.0.0

总结：窗口函数能力矩阵

需求	推荐函数
排名、Top N	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`
累计和、移动平均	`SUM() OVER (ORDER BY ... ROWS ...)`
前后行比较	`LAG()`, `LEAD()`
分组统计（保留明细）	`AVG()`, `COUNT()` + `PARTITION BY`
分位、分布	`PERCENT_RANK()`, `NTILE()`

终极口诀：
“分组用 PARTITION，排序靠 ORDER BY，帧控精细范围，函数各显神通。”

发表于 2025-12-11 13:40 jzssuanfa 阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

详细介绍：第五章:MySQL DQL 进阶 —— 动态计算与分类（IF 与 CASE WHEN）多表查询

条件判断 —— 动态计算与分类（IF 与 CASE WHEN）

一、IF() 函数（MySQL 简洁写法）

示例 1：用户状态标记

示例 2：订单是否大额

二、CASE WHEN 语句（SQL 标准，推荐用于复杂逻辑）

1. 简单 CASE（等值判断）

2. 搜索 CASE（条件判断，更常用）

三、在聚合中使用条件判断（强大组合！）

统计不同类型订单数量

按用户分组 + 分类统计

四、IF vs CASE WHEN 对比

五、常见错误与避坑

❌ 错误 1：忘记 END

❌ 错误 2：在 WHERE 中直接用别名（即使来自 CASE）

六、小结：条件判断能力清单

二、环境准备（基于第三章的数据库）

三、多表连接（JOIN）—— 关联数据的基石

1. INNER JOIN：只返回匹配的记录

2. LEFT JOIN：保留左表所有记录（即使无匹配）

3. RIGHT JOIN 与 FULL OUTER JOIN

四、子查询（Subquery）—— 查询中的查询

1. 单行单列子查询（Scalar Subquery）

2. 单行多列子查询

3. 多行单列子查询

4. 多行多列子查询

总结对比表：

⚠️ 注意事项：

案例: 场景----找出消费总额超过平均值的用户

方法 1：WHERE 中的标量子查询

方法 2：FROM 中的派生表（更高效）

五、聚合分析（GROUP BY + 聚合函数）

常用聚合函数：

实战：用户消费画像

六、窗口函数（MySQL 8.0+）—— 分析利器

场景：给用户按消费排名

其他常用窗口函数：

四大类窗口函数详解

第一类：排名函数（Ranking Functions）

示例：用户订单排名

第二类：聚合函数（作为窗口函数）

示例：累计销售额（Running Total）

第三类：偏移函数（Offset / Navigation）

示例：订单金额环比变化

第四类：分布函数（Distribution）

示例：用户消费百分位

OVER() 子句深度解析

1. PARTITION BY：分组（窗口边界）

2. ORDER BY：窗口内排序

3. 帧范围（Frame Clause）：精确控制窗口行

示例：3日移动平均

实战案例

案例1：每个用户的最新订单

案例2：销售额 Top 3 用户（允许并列）

案例3：订单金额高于用户平均值的记录

常见误区与性能提示

❌ 误区1：认为窗口函数会改变行数

❌ 误区2：在 WHERE 中使用窗口函数

⚡ 性能提示：

MySQL 版本要求

总结：窗口函数能力矩阵

导航

一、`IF()` 函数（MySQL 简洁写法）

二、`CASE WHEN` 语句（SQL 标准，推荐用于复杂逻辑）

1. 简单 `CASE`（等值判断）

2. 搜索 `CASE`（条件判断，更常用）

四、`IF` vs `CASE WHEN` 对比

❌ 错误 1：忘记 `END`

❌ 错误 2：在 `WHERE` 中直接用别名（即使来自 CASE）

1. `INNER JOIN`：只返回匹配的记录

2. `LEFT JOIN`：保留左表所有记录（即使无匹配）

3. `RIGHT JOIN` 与 `FULL OUTER JOIN`

方法 1：`WHERE` 中的标量子查询

方法 2：`FROM` 中的派生表（更高效）

`OVER()` 子句深度解析

1. `PARTITION BY`：分组（窗口边界）

2. `ORDER BY`：窗口内排序

❌ 误区2：在 `WHERE` 中使用窗口函数