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 强大的数据处理能力！

到此这篇关于mysql高级查询之join、子查询、窗口函数的文章就介绍到这了,更多相关mysql高级查询join、子查询、窗口函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！