不求甚解

此博客为个人学习之用，如与其他作品雷同，纯属巧合。

导航

Q：sql优化，传统的【嵌套子查询】VS【窗口函数+派生表关联】

Posted on 2025-07-08 16:50  三年三班王小朋  阅读(5)  评论(0)    收藏  举报

在实际开发运维中，我们经常遇到这样的需求：从一张数据表中获取“每个分组中的最新记录”或者“每组中某个字段值最大的那条完整数据”。面对这类问题，很多开发者习惯使用嵌套子查询来实现。但其实还有一种更清晰、性能更好的方法 —— **使用窗口函数+派生表进行关联查询**。

一、什么是窗口函数？

窗口函数是一种可以在不对原始数据进行合并的前提下，对一组相关的行进行排序、编号或计算的方法。它不像普通的聚合函数（如 `SUM()`、`MAX()`）那样会把多行压缩成一行，而是保留每一行的数据，并在其基础上进行额外的分析。

常见的窗口函数有：

  • `ROW_NUMBER()`：为每一行分配一个唯一的序号；
  • `RANK()` 和 `DENSE_RANK()`：用于排名；
  • `FIRST_VALUE()` 和 `LAST_VALUE()`：取窗口内的第一个或最后一个值；
  • `LEAD()` 和 `LAG()`：访问当前行的前一行或后一行数据；
  • `SUM() OVER()`、`AVG() OVER()` 等：滚动统计类函数。

例如，如果我们想给每组数据按时间倒序排个名，就可以这样写：

ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC)

这段代码的意思是：

- 按照“分组字段”进行分组；
- 在每一组内，按照“排序字段”降序排列；
- 给每一行分配一个行号。

这个功能非常适合用来筛选“每组最新的那条记录”。

 二、什么是派生表？

派生表就是写在主查询中的一个子查询，它会被先执行，然后作为一张临时表参与后续查询。

比如：

SELECT *
FROM (
SELECT 字段1, 字段2, ROW_NUMBER() OVER(...) AS 行号
FROM 数据表
) AS 临时表名
WHERE 临时表名.行号 = 1;

这里的括号部分就是一个派生表，它会在主查询之前先运行，生成一个带行号的中间结果集，供外部查询使用。

三、如何将窗口函数和派生表结合使用？

我们可以将窗口函数的结果作为派生表的一部分，然后通过左连接的方式，把主表和这个派生表关联起来，从而拿到我们需要的每组最新记录。

示例场景：

假设我们有两个表：

- 主表 `a`：存储就诊记录，包含字段 `djlsh`（记录流水号）等；
- 明细表 `yh_zy_jzjl`：存储每次就诊的详细信息，包含 `djlsh`、`xtsjgxsj`（更新时间）、`dqksbm`（科室编码）、`zyysxm`（医生姓名）等字段。

我们的目标是：**对于主表中的每一条记录，在明细表中找到对应 djlsh 的最新一条记录，并获取其中的科室和医生信息。**

实现步骤如下：

1. 对明细表 `yh_zy_jzjl` 按照 `djlsh` 分组；
2. 在每一组中，按照 `xtsjgxsj` 时间降序排列，并使用 `ROW_NUMBER()` 为每一行打上序号；
3. 只保留序号为 1 的记录（即每组最新的一条）；
4. 将这些记录作为一个派生表；
5. 最后将主表 `a` 和这个派生表进行左连接，连接条件是 `djlsh` 相同；
6. 获取需要的字段。

SQL 写法如下：

SELECT a.字段1, a.字段2, jz.科室编码, jz.医生姓名, jz.更新时间
FROM a
LEFT JOIN (
SELECT djlsh, dqksbm AS 科室编码, zyysxm AS 医生姓名, xtsjgxsj AS 更新时间,
ROW_NUMBER() OVER(PARTITION BY djlsh ORDER BY xtsjgxsj DESC) AS 行号
FROM yh_zy_jzjl
) jz ON a.djlsh = jz.djlsh AND jz.行号 = 1;

四、派生表 + 窗口函数和嵌套子查询的对比

场景派生表 + 窗口函数嵌套子查询说明
深翻页（Deep Pagination） ✅ 高效 ❌ 低效 派生表可一次性计算行号，分页效率高；嵌套子查询每次都要重新扫描数据，翻页越深越慢。
多字段返回 ✅ 支持一次获取多个字段 ❌ 需多次子查询 派生表只需一次子查询即可提取多个字段；嵌套方式需为每个字段单独写子查询，重复且低效。
大数据量处理 ✅ 性能较好 ❌ 性能差 派生表只扫描一次主表，适合大数据；嵌套子查询每条记录都可能触发多次扫描，性能下降明显。
取每组第一条 / 第二条 / TOP N ✅ 非常方便 ❌ 实现复杂 使用 ROW_NUMBER()、RANK() 等窗口函数轻松实现；嵌套方式难以控制“第几条”。
代码结构与可维护性 ✅ 清晰易读 ❌ 冗余难维护 子查询逻辑集中，易于调试和扩展；嵌套写法分散，容易出错。
兼容性 ⚠️ 要求数据库支持窗口函数 ✅ 兼容性强 MySQL 8.0+、PostgreSQL、Oracle 等才支持窗口函数；嵌套子查询适用于几乎所有数据库版本。
去重或筛选最新记录 ✅ 高效精准 ⚠️ 容易出错 可通过 rn = 1 精确获取最新记录；嵌套子查询在有重复时间/值时可能返回多条。
排序后关联其他表 ✅ 支持先排序再连接 ⚠️ 复杂难实现 派生表中可先排序编号，再与主表连接；嵌套方式难以表达这种逻辑。
聚合 + 排序组合需求 ✅ 支持丰富组合 ⚠️ 功能受限 可结合 SUM() OVER()、AVG() 等滚动统计；嵌套方式只能做简单聚合。

 

五、适用数据库版本

这种写法依赖于数据库对窗口函数的支持，适用于以下数据库系统：

- MySQL 8.0 及以上版本；
- PostgreSQL；
- Oracle；
- SQL Server；
- 较新的 SQLite 版本；
- 以及各种数仓工具如 Hive、BigQuery、ClickHouse 等。

如果你使用的是较老的 MySQL 5.x 版本，则无法使用窗口函数，只能退而求其次使用嵌套子查询。