MySQL 高级查询：JOIN、子查询、窗口函数
MySQL 高级查询：JOIN、子查询、窗口函数在日常开发中，单表查询往往无法满意业务需求。通过高级查询技能，MySQL 能够机动地从多个表中获取数据、举行复杂的数据筛选和分析。本文将重点介绍三种高级查询方式：JOIN（毗连查询）、子查询 和 窗口函数，并提供现实案例帮助你更好地理解和应用这些技能。
1. JOIN（毗连查询）
JOIN 允许我们在 SQL 语句中将两个或多个表通过相关联的列举行组合，从而在一条查询中获取多表数据。MySQL 中常见的 JOIN 类型包罗：
1.1 内毗连（INNER JOIN）
[*]原理：返回两个表中满意毗连条件的记录。
[*]示例：SELECT o.order_id, o.order_date, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
上述查询返回全部订单及其对应客户名称，仅当订单和客户存在匹配关系时才会返回结果。
1.2 左毗连（LEFT JOIN）
[*]原理：返回左表的全部记录，纵然右表中没有匹配也会显示 NULL。
[*]示例：SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
此查询列出全部客户，纵然有些客户没有订单，相关订单字段将显示为 NULL。
1.3 右毗连（RIGHT JOIN）
[*]原理：与左毗连类似，不过返回右表全部记录，左表未匹配部门显示 NULL。
[*]示例：SELECT o.order_id, c.customer_name
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.customer_id;
这种毗连方式在现实开发中较少使用，多数场景可以通过调整 LEFT JOIN 的顺序来实现雷同结果。
1.4 自毗连（Self JOIN）
[*]原理：同一张表中不同记录间的关联查询，通常用于查找具有层级或关系的数据。
[*]示例：SELECT e1.employee_name AS Manager, e2.employee_name AS Subordinate
FROM employees AS e1
INNER JOIN employees AS e2 ON e1.employee_id = e2.manager_id;
该查询展示了管理者与其部属之间的关系。
2. 子查询
子查询（Subquery）是嵌套在其他 SQL 语句内部的查询语句，通常用于将一个查询的结果作为条件或数据源。根据使用位置，子查询可分为以下几种：
2.1 标量子查询
[*]特点：返回单个值，可以在 WHERE 或 SELECT 子句中直接使用。
[*]示例：SELECT order_id, order_date
FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE customer_name = '张三');
此查询将客户名称为“张三”的客户 ID 提取出来，并用于过滤订单表中的记录。
2.2 列表子查询
[*]特点：返回一列值，可以用于 IN 或 NOT IN 条件中。
[*]示例：SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = '北京');
该查询筛选出全部来自北京的客户的订单。
2.3 表子查询
[*]特点：返回一个结果集，通常用于 FROM 子句中充当临时表。
[*]示例：SELECT t.customer_id, t.total_orders
FROM (
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
) AS t
WHERE t.total_orders > 5;
这里的子查询先统计每个客户的订单数量，再过滤出订单数大于 5 的客户。
2.4 相关子查询
[*]特点：子查询依赖于外层查询的数据，每行记录都将实行一次子查询。
[*]示例：SELECT e.employee_id, e.employee_name,
(SELECT COUNT(*) FROM orders o WHERE o.salesperson_id = e.employee_id) AS order_count
FROM employees AS e;
该查询为每个销售人员统计其负责的订单数量。
3. 窗口函数
MySQL 从 8.0 版本开始支持窗口函数（Window Functions），这使得在不使用子查询的环境下直接对查询结果举行分组统计、排名等操纵成为可能。
3.1 常见窗口函数
[*] ROW_NUMBER()：为结果集中的每一行返回一个唯一的序号。
SELECT order_id, order_date,
ROW_NUMBER() OVER (ORDER BY order_date) AS row_num
FROM orders;
该查询按照订单日期为每个订单分配一个行号。
[*] RANK() 与 DENSE_RANK()：用于排名，但在存在雷同值时处理处罚方式略有不同。RANK 会跳过排名，而 DENSE_RANK 不跳过。
SELECT customer_id, total_spent,
RANK() OVER (ORDER BY total_spent DESC) AS rank
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS spending;
[*] SUM()、AVG()、MAX()、MIN() 等聚合函数：可以作为窗口函数使用，计算每个分组内的累计值或均匀值等。
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;
上述查询展示了订单金额的累计总和，可以用于生成报表或趋势图。
3.2 使用场景
[*]排名和排序：对销售额、得分或其他指标举行排序和排名。
[*]累计求和：生成动态的累计值，如销售额的逐日累加。
[*]分区统计：在不使用 GROUP BY 的环境下，对数据举行分区统计，保存详细数据行。
4. 现实案例：综合应用
假设你需要生成一个销售报表，其中包含每个销售人员的订单总额及其在各自地域内的排名，可以团结子查询与窗口函数来实现：
WITH SalesData AS (
SELECT salesperson_id, region, SUM(amount) AS total_sales
FROM orders
GROUP BY salesperson_id, region
)
SELECT salesperson_id, region, total_sales,
RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS sales_rank
FROM SalesData;
这里，**CTE（公用表表达式）**先统计出每个销售人员在各个地域内的订单总额，然后使用窗口函数按地域举行分区并对总销售额举行排名，帮助管理者快速识别出每个地域的销售冠军。
5. 总结
[*]JOIN 使得多表关联查询变得简单、高效，可以通过不同类型的毗连满意各种业务需求。
[*]子查询 提供了机动的数据筛选和过滤方式，适用于对单个数据项或整个结果集的处理处罚。
[*]窗口函数 则在 MySQL 8.0 之后引入，为数据统计、排名和累计计算提供了更直观、更高效的办理方案。
通过深入把握这三种高级查询技能，你可以大幅提升 MySQL 查询的复杂度与机动性，从而更好地支持复杂业务场景和数据分析需求。接待在实践中不停尝试和优化，充实利用 MySQL 强大的数据处理处罚本领！
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]