MySQL高级查询之JOIN、子查询、窗口函数实际案例

2025-04-14 16:50
文章标签 mysql 函数 查询 窗口 高级 案例 join database 实际

本文主要是介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》：本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,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;YBKuacoq
    上述查询返回所有订单及其对应客户名称，仅当订单和客户存在匹配关系时才会返回结果。

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 oChina编程 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 的情况下，对数据进行分区统http://www.chinasem.cn计，保留详细数据行。

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 使得多表关联查询变得简单、高效，可以通过不同类型的连接满足各种业务需求。
  • 子查询 提供了灵活的数据筛选和过滤方式，适用于对单个数据项或整个结果集的处理。http://www.chinasem.cn
  • 窗口函数 则在 MySQL 8.0 之后引入，为数据统计、排名和累计计算提供了更直观、更高效的解决方案。

通过深入掌握这三种高级查询技术，你可以大幅提升 MySQL 查询的复杂度与灵活性，从而更好地支持复杂业务场景和数据分析需求。欢迎在实践中不断尝试和优化，充分利用 MySQL 强大的数据处理能力！

到此这篇关于MySQL高级查询之JOIN、子查询、窗口函数的文章就介绍到这了,更多相关MySQL高级查询JOIN、子查询、窗口函数内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望js大家以后多多支持China编程(www.chinasem.cn)！

这篇关于MySQL高级查询之JOIN、子查询、窗口函数实际案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1154213。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
Linux join命令的使用及说明

Linux join命令的使用及说明

《Linuxjoin命令的使用及说明》`join`命令用于在Linux中按字段将两个文件进行连接,类似于SQL的JOIN,它需要两个文件按用于匹配的字段排序,并且第一个文件的换行符必须是LF,`jo... 目录一. 基本语法二. 数据准备三. 指定文件的连接key四.-a输出指定文件的所有行五.-o指定输出
阅读更多...
从基础到高级详解Go语言中错误处理的实践指南

从基础到高级详解Go语言中错误处理的实践指南

《从基础到高级详解Go语言中错误处理的实践指南》Go语言采用了一种独特而明确的错误处理哲学,与其他主流编程语言形成鲜明对比,本文将为大家详细介绍Go语言中错误处理详细方法,希望对大家有所帮助... 目录1 Go 错误处理哲学与核心机制1.1 错误接口设计1.2 错误与异常的区别2 错误创建与检查2.1 基础
阅读更多...
Python函数作用域与闭包举例深度解析

Python函数作用域与闭包举例深度解析

《Python函数作用域与闭包举例深度解析》Python函数的作用域规则和闭包是编程中的关键概念,它们决定了变量的访问和生命周期,：本文主要介绍Python函数作用域与闭包的相关资料,文中通过代码... 目录1. 基础作用域访问示例1：访问全局变量示例2：访问外层函数变量2. 闭包基础示例3：简单闭包示例4
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2