assets/2026-07-01-医数智联系列开篇-医院数字化工作者的工具箱_2026-07-01_18-28-11.jpg

医数智联·第 08 期:MySQL 进阶 —— 首页数据 5 大常用查询,JOIN/子查询/窗口函数

[!ABSTRACT] 核心摘要

  • 本期主题:MySQL 进阶——把多张表拼起来,做交叉分析、Top N、累计、同比
  • 核心结论:单表查询只能解决 30% 的医院数据问题,70% 需要「多表 JOIN」。JOIN 是医院数据分析的「灵魂技能」
  • 3 大核心技能:JOIN(表关联)+ 子查询(嵌套查询)+ 窗口函数(行内统计)
  • 5 大实战查询:交叉分析、Top N、累计汇总、同比环比、留存分析
  • 阅读时长:约 10 分钟

一、场景引入:为什么单表查询远远不够?

[!example] 一个真实的周四下午
质管办小张学会了基础 SQL(见第 07 期),他想统计「上月每个科室的主诊错误率,以及对应的责任医生职称分布」。

他发现首页数据被分散在 4 张表里:

表名 内容
首页主表 病案号、出院日期、科室、主诊 ICD
诊断表 病案号、所有诊断(主诊+副诊)、诊断类型
医生表 医生工号、姓名、职称、所属科室
病案审核表 病案号、审核状态、错误分值、审核日期

他在 首页主表 里只能拿到科室和主诊 ICD,但要拿「职称」必须关联 医生表,要拿「错误分值」必须关联 病案审核表。

「跨表」这件事,单表 SQL 搞不定。

这是医院数据工作的典型困境——真实数据被规范化拆分成多张表,任何有价值的分析都要跨表。

[!quote] 狼叔的判断
“JOIN 是数据库的’灵魂’,不会 JOIN = 只会用 30% 的数据库能力。” 这一期,带你掌握 JOIN/子查询/窗口函数,把 SQL 能力从「单表」升级到「跨表」。

二、工具原理:JION/子查询/窗口函数是什么?

2.1 JOIN:把多张表「拼」起来

[!info] JOIN 是什么?
JOIN 是 SQL 的核心——它把两张表按「关联字段」拼成一张虚拟表。

最常见的场景:用「病案号」把 首页主表 和 诊断表 拼起来,用「医生工号」把 首页主表 和 医生表 拼起来。

2.1.1 INNER JOIN(只保留匹配的)

1
2
3
4
5
6
7
8
9
SELECT
  a.病案号,
  a.出院日期,
  a.科室,
  b.诊断ICD,
  b.诊断类型
FROM 首页主表 a
INNER JOIN 诊断表 b ON a.病案号 = b.病案号
WHERE a.出院日期 >= '2026-06-01';

翻译:把首页主表和诊断表拼起来,只保留两边都有「病案号」匹配的记录。

2.1.2 LEFT JOIN(保留左表所有)

1
2
3
4
5
6
7
SELECT
  a.病案号,
  a.出院日期,
  b.诊断ICD
FROM 首页主表 a
LEFT JOIN 诊断表 b ON a.病案号 = b.病案号
WHERE a.出院日期 >= '2026-06-01';

翻译:保留所有首页记录,即使没有对应的诊断记录(诊断字段会是 NULL)。

[!TIP] LEFT JOIN 是医院最常用的
因为我们经常要查「首页有多少没有诊断」之类的反向问题。

2.1.3 图解 JOIN 类型

1
2
3
4
5
6
7
8
9
     INNER JOIN                LEFT JOIN
┌──────┬──────┐           ┌──────┬──────┐
│  A   │  B   │           │AAAAA │  B   │
│      ├──────┤           │AAAAA ├──────┤
│      │ 共同 │           │AAAAA │ 共同 │
│      ├──────┤           │AAAAA ├──────┤
│      │      │           │AAAAA │      │
└──────┴──────┘           └──────┴──────┘
只返回共同部分          返回所有 A + 共同部分

2.2 子查询:嵌套查询

[!info] 子查询是什么?
把一个 SELECT 的结果当作另一个 SELECT 的「条件」或「数据源」。

常见用途:「先查一组 ID,再用这组 ID 去过滤另一张表」。

1
2
3
4
5
6
7
8
9
10
11
-- 找出「上月错误数 ≥ 5 例的责任医生」的所有首页记录
SELECT *
FROM 首页主表
WHERE 责任医生工号 IN (
  SELECT 责任医生工号
  FROM 首页主表
  WHERE 主诊错误标记 = 1
    AND 出院日期 >= '2026-06-01'
  GROUP BY 责任医生工号
  HAVING COUNT(*) >= 5
);

翻译:子查询先找出错误数 ≥ 5 的医生工号,主查询再过滤这些医生的所有首页记录。

2.3 窗口函数:行内统计

[!info] 窗口函数是什么?
普通的 GROUP BY 会把多行「压缩」成一行;窗口函数则在不压缩行的前提下,做行内统计(比如「这一行的数据在所有行里的排名」)。

MySQL 8.0+ 才支持,早期版本没有。

1
2
3
4
5
6
SELECT
  责任医生,
  主诊错误标记,
  出院日期,
  ROW_NUMBER() OVER (PARTITION BY 责任医生 ORDER BY 出院日期 DESC) AS 行号
FROM 首页主表;

翻译:每位责任医生的记录按出院日期倒序编号,1 = 最近一次,2 = 上一次,以此类推。

三、医院实战:5 大常用查询

3.1 查询一:交叉分析(科室 × 职称 × 错误率)

[!example] 实战 ①:三维交叉分析
需求:统计上月每个科室、每个职称的医生,主诊错误率是多少?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
  a.科室,
  c.职称,
  COUNT(*) AS 总病案数,
  SUM(CASE WHEN a.主诊错误标记 = 1 THEN 1 ELSE 0 END) AS 错误数,
  ROUND(
    SUM(CASE WHEN a.主诊错误标记 = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
    2
  ) AS 错误率_百分比
FROM 首页主表 a
LEFT JOIN 医生表 c ON a.责任医生工号 = c.医生工号
WHERE a.出院日期 >= '2026-06-01'
  AND a.出院日期 < '2026-07-01'
GROUP BY a.科室, c.职称
ORDER BY 错误率_百分比 DESC;

关键技巧:

  • SUM(CASE WHEN ... THEN 1 ELSE 0 END) 是「条件求和」的经典写法
  • * 100.0 必须带 .0,否则整数除法会丢精度
  • ROUND(..., 2) 保留 2 位小数

3.2 查询二:Top N(每个科室错误最多的 3 个医生)

[!example] 实战 ②:Top N 排名
需求:找出每个科室主诊错误数 Top 3 的医生。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH 医生错误数 AS (
  SELECT
    a.科室,
    a.责任医生工号,
    c.医生姓名,
    COUNT(*) AS 错误数,
    ROW_NUMBER() OVER (
      PARTITION BY a.科室
      ORDER BY COUNT(*) DESC
    ) AS 科室排名
  FROM 首页主表 a
  LEFT JOIN 医生表 c ON a.责任医生工号 = c.医生工号
  WHERE a.主诊错误标记 = 1
    AND a.出院日期 >= '2026-06-01'
  GROUP BY a.科室, a.责任医生工号, c.医生姓名
)
SELECT *
FROM 医生错误数
WHERE 科室排名 <= 3
ORDER BY 科室, 科室排名;

关键技巧:

  • WITH ... AS (...) 是 CTE(Common Table Expression),把子查询「具名化」
  • ROW_NUMBER() OVER (PARTITION BY 科室 ORDER BY 错误数 DESC) 在每个科室内部按错误数排名

3.3 查询三:累计汇总(每日累计)

[!example] 实战 ③:累计统计
需求:按日统计本月每天的错误数,并显示累计错误数。

1
2
3
4
5
6
7
8
9
SELECT
  出院日期,
  COUNT(*) AS 当日错误数,
  SUM(COUNT(*)) OVER (ORDER BY 出院日期) AS 累计错误数
FROM 首页主表
WHERE 主诊错误标记 = 1
  AND 出院日期 >= '2026-06-01'
GROUP BY 出院日期
ORDER BY 出院日期;

关键技巧:SUM(COUNT(*)) OVER (ORDER BY 出院日期) 是「累计求和」的窗口函数写法。

3.4 查询四:同比环比

[!example] 实战 ④:同比环比
需求:本月 vs 上月,本月错误率 vs 上月错误率,变化百分比?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH 本月 AS (
  SELECT COUNT(*) AS 总数,
         SUM(CASE WHEN 主诊错误标记 = 1 THEN 1 ELSE 0 END) AS 错误数
  FROM 首页主表
  WHERE 出院日期 >= '2026-07-01' AND 出院日期 < '2026-08-01'
),
上月 AS (
  SELECT COUNT(*) AS 总数,
         SUM(CASE WHEN 主诊错误标记 = 1 THEN 1 ELSE 0 END) AS 错误数
  FROM 首页主表
  WHERE 出院日期 >= '2026-06-01' AND 出院日期 < '2026-07-01'
)
SELECT
  本月.错误数 / 本月.总数 * 100 AS 本月错误率,
  上月.错误数 / 上月.总数 * 100 AS 上月错误率,
  ROUND(
    (本月.错误数 / 本月.总数 - 上月.错误数 / 上月.总数) * 100,
    2
  ) AS 变化_百分点
FROM 本月, 上月;

3.5 查询五:留存分析(7 天再入院)

[!example] 实战 ⑤:7 天再入院分析
需求:找出 7 天内再入院的患者(质量重要指标)。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
  a.病案号,
  a.出院日期 AS 首次出院,
  b.入院日期 AS 再入院,
  DATEDIFF(b.入院日期, a.出院日期) AS 间隔天数
FROM 首页主表 a
INNER JOIN 首页主表 b
  ON a.病案号 = b.病案号
  AND b.入院日期 > a.出院日期
  AND b.入院日期 <= DATE_ADD(a.出院日期, INTERVAL 7 DAY)
WHERE a.出院日期 >= '2026-06-01'
ORDER BY a.病案号;

[!TIP] 自连接(Self Join)
同一张表和自己 JOIN,别名必须不同(这里是 a 和 b)。自连接是查「同一张表内的关联关系」的标准技巧。

四、避坑指南:5 个让新手”踩雷”的常见错误

[!warning] 雷区 ①:JION 后忘记别名,字段冲突
症状:Column '病案号' in field list is ambiguous(字段模糊)。
解决:所有字段都加表前缀,比如 a.病案号、b.诊断类型。

[!warning] 雷区 ②:INNER JOIN 把数据「吃掉」了
症状:用 INNER JOIN 关联「首页主表」和「诊断表」,返回的行数比预期少。
原因:INNER JOIN 只保留匹配的记录,如果某些首页没有诊断记录,会被过滤掉。
解决:用 LEFT JOIN,保留所有首页。

[!warning] 雷区 ③:子查询返回多行,用 = 比较
症状:WHERE 责任医生工号 = (SELECT ...) 报错 “Subquery returns more than 1 row”。
解决:用 IN 替代 =:WHERE 责任医生工号 IN (SELECT ...)。

[!warning] 雷区 ④:窗口函数忘记 PARTITION BY
症状:ROW_NUMBER() OVER (ORDER BY 错误数 DESC) 给整个表排名,而不是按科室分组。
解决:加 PARTITION BY 字段 才能分组排名。

[!warning] 雷区 ⑤:日期字段做差忘转 DATE
症状:DATEDIFF(出院日期, 入院日期) 返回天数对,但如果字段带时间,会算出「4.5 天」之类的小数。
解决:用 DATE() 转日期:DATEDIFF(DATE(出院日期), DATE(入院日期))。

五、下期预告

[!quote] 第 09 期
MySQL + 首页主诊错误率分析 —— 实战案例

把第 07、08 期的技能全部串起来,从「写 SQL」到「出一份院长能直接看的月度报表」。

[!TIP] 互动
如果你觉得这篇「医数智联·第 08 期」对你有用,请点赞、在看、转发给科里的同事。

我是白衣狼,一个用数字化工具把医院管理做「轻」的实战派。咱们下期见。