一、汇总：一条 SQL 出班级平均分

COUNT(DISTINCT s.id) 和 COUNT(sc.id) 两个都要有。 前者是「人数」，后者是「人次」——4 个人 3 科，人数 4、人次 12。 AVG 的分母是「行数」不是「人数」。 所以缺考的学生会影响平均分—— 如果某科他缺考没录，平均分是拿剩下的人算的，看起来偏高。 要不要把缺考当 0 分算，是业务口径问题，必须问清楚。

二、明细里的排名：窗口函数一次算完

RANK 和 ROW_NUMBER 的区别在并列。 两个人并列第 2，RANK 给 2、2、4，ROW_NUMBER 给 2、3、4。 成绩排名一般用 RANK——并列就该并列，硬排出个先后反而会被质疑。 但分页必须用 ROW_NUMBER—— 因为分页要「每行一个不同的序号」，重复的序号会导致翻页时有的行出现两次。 同一个函数，两个场景，选错就出问题。

三、汇总和明细怎么放到一个页面

教材用的是第一种，这一版也是。 理由是「两次查询之间数据可能变」这个问题，在这个场景下可以接受—— 班级平均分差几十毫秒没人看得出来。 但如果是「账户余额和交易明细」，两次查询就不行了—— 余额和明细对不上会被当成 bug。 那种场景必须一次查询，或者一个事务里两次。

四、学生只看自己：一个页面，一个条件

这一句里的学号从 Session 来，不从 URL 来。 这是全章讲了五遍的那件事，在这里最后一次强调。 同一个页面，管理员进来看到全级、教师看到全班、学生看到自己—— 三者的差别只在 WHERE 那一段。 这就是「一个页面 + 数据范围」的全部含义。

五、全书收口：三个项目共用一套骨架

三张表里最值得看的是「事务边界」那一行。 三个业务完全不同，但「哪些操作必须一起成功」这个判断是一模一样的思路。 这就是从项目里能带走的东西。 「权限」那一行也值得看： 前两个项目只做到「菜单按角色」， 第三个项目多了一层「数据范围」——因为第一次出现了「同一页面、不同数据」。 这不是设计变复杂了，是需求变复杂了。

六、最后：这一课到底学了什么

四层里，语言层最容易忘（几个月不写就生疏），工程层最难学（要靠项目攒）。 而中间两层是「一旦懂了就不会忘」的—— 因为它们讲的是「数据库为什么这样设计」，不是「语法怎么写」。 所以复习这一课，别背语法。 翻 14 章的索引、15 章的事务、20 章的那三张骨架表—— 这些换个数据库、换个语言，都还在。

七、动手 Demo：班级汇总、明细排名、三种数据范围

这一段跑三件事： 班级汇总（人数 vs 人次）、明细里的班内排名与全级排名、同一个查询在三种会话下看到的三份数据。 最后那一节是这一课的收口——四层知识、三个项目、一套骨架。

# ① 汇总：人数 vs 人次
sql = '''SELECT c.cname,
               COUNT(DISTINCT s.id) AS 人数,
               COUNT(sc.id)         AS 条数,
               ROUND(AVG(sc.score), 2) AS 平均分
          FROM tb_class c
          JOIN tb_student s ON s.class_id = c.id
          JOIN tb_score  sc ON sc.stu_id  = s.id
         GROUP BY c.id ORDER BY 平均分 DESC'''

# ② 明细 + 排名（窗口函数）
sql = '''SELECT cname, sname, score,
               RANK() OVER (PARTITION BY cname ORDER BY score DESC) AS 班内,
               RANK() OVER (ORDER BY score DESC)                     AS 全级
          FROM v_score WHERE subject = '数学'
         ORDER BY cname, 班内'''

# ③ 一个查询，三种数据范围
def scores(sess):
    sql, args = BASE_SQL, ['数学']
    if sess['role'] == '学生':
        sql += ' AND sno = (SELECT sno FROM tb_student WHERE id = ?)'
        args.append(sess['stu_id'])
    elif sess['role'] == '教师':
        sql += ' AND cname = (SELECT cname FROM tb_class WHERE id = ?)'
        args.append(sess['class_id'])
    return c.execute(sql, args).fetchall()

实跑输出：

--------------------------------------------------------------------
按班级汇总与明细：全书的收口
--------------------------------------------------------------------


  ① 汇总：一条 SQL 出班级平均分
  --------------------------------------------------------------------


  1> SELECT c.cname,
         COUNT(DISTINCT s.id) AS 人数,
         COUNT(sc.id) AS 条数,
         ROUND(AVG(sc.score), 2) AS 平均分
     FROM tb_class c JOIN tb_student s ON s.class_id = c.id JOIN tb_score sc ON sc.stu_id = s.id
     GROUP BY c.id
     ORDER BY 平均分 DESC
  cname            人数  条数  平均分
  -----------------------------------
  2026级软件1班    3     9     84.78
  2025级计算机1班  2     6     83.33
  2026级计算机1班  4     12    82.33
  2026级计算机2班  3     9     80.67
  (4 行受影响)
  COUNT(DISTINCT s.id) 和 COUNT(sc.id) 两个都要有。
  前者是「人数」，后者是「人次」——4 个人 3 科，人数 4、人次 12。
  AVG 的分母是「行数」不是「人数」。
  所以缺考的学生会影响平均分——如果某科他缺考没录，平均分是拿剩下的人算的，看起来偏高。
  要不要把缺考当 0 分算，是业务口径问题，必须问清楚。

  ② 明细里的排名：窗口函数一次算完
  --------------------------------------------------------------------


  1> SELECT cname,
         sname,
         score,
         RANK() OVER (PARTITION BY cname ORDER BY score DESC) AS 班内排名,
         RANK() OVER (ORDER BY score DESC) AS 全级排名
     FROM v_score
     WHERE subject = '数学'
     ORDER BY cname, 班内排名
  cname            sname   score  班内排名  全级排名
  --------------------------------------------------
  2025级计算机1班  孙志强  88     1         4
  2025级计算机1班  马晓雯  79     2         8
  2026级计算机1班  张一鸣  92     1         2
  2026级计算机1班  李思彤  88     2         4
  2026级计算机1班  赵雨欣  78     3         9
  2026级计算机1班  王浩然  65     4         12
  2026级计算机2班  陈子轩  96     1         1
  2026级计算机2班  杨博文  82     2         7
  2026级计算机2班  刘佳怡  74     3         10
  2026级软件1班    黄梓涵  90     1         3
  2026级软件1班    吴俊杰  85     2         6
  2026级软件1班    徐梦琪  71     3         11
  (12 行受影响)
  ! RANK() 是 SQL Server 2005 起就有的窗口函数；
  SQLite 3.25 起才支持，更老的版本要用自连接数「比我高的人有几个」。
  窗口函数一次扫表就能算出排名，比自连接快得多，也好读得多。
  PARTITION BY 就是「分组」，ORDER BY 就是「组内按什么排」。
  RANK 和 ROW_NUMBER 的区别在并列： 两个人并列第 2，RANK 给 2、2、4，ROW_NUMBER 给 2、3、4。
  成绩排名一般用 RANK——并列就该并列，硬排出个先后反而会被质疑。

  ③ 汇总和明细怎么放到一个页面
  --------------------------------------------------------------------
| 做法 | 优点 | 缺点 |
|---|---|---|
| 两次查询，汇总在上、明细在下 | 简单，SQL 各管一段 | 两次往返 |
| 一次查询带 GROUPING SETS | 一次往返 | SQL 复杂，代码难读 |
| 明细里带汇总列（窗口函数） | 一行里两个粒度都有 | 汇总值重复出现 |

  教材用的是第一种，这一版也是。
  理由是「两次查询之间数据可能变」这个问题，
  在这个场景下可以接受——班级平均分差几十毫秒没人看得出来。
  但如果是「账户余额和交易明细」，两次查询就不行了——余额和明细对不上会被当成 bug。
  那种场景必须一次查询，或者一个事务里两次。

  ④ 学生只看自己：一个页面，一个条件
  --------------------------------------------------------------------


  1> SELECT subject, term, score FROM v_score WHERE sno = '20260101' ORDER BY subject
  subject  term         score
  ---------------------------
  数学     2026-2027-1  92
  英语     2026-2027-1  79
  语文     2026-2027-1  88
  (3 行受影响)

  -- 页面上这一句才是安全线：
  -- WHERE sno = (Session 里的学号)，绝不从 URL 取
  这一句里的学号从 Session 来，不从 URL 来。 这是本章讲了五遍的那件事，在这里最后一次强调。
  同一个页面，管理员进来看到全级、教师看到全班、学生看到自己——三者的差别只在 WHERE 那一段。
  这就是「一个页面 + 数据范围」的全部含义。

  ⑤ 全书收口：三个项目共用一套骨架
  --------------------------------------------------------------------
| 骨架 | 酒店（18） | 人事（19） | 成绩（20） |
|---|---|---|---|
| 表设计先行 | 房／客／入住的链 | 部门／员工／薪酬 | 班级／学生／成绩 |
| 约束兜底 | 房号唯一、价格>0 | 工号唯一、职级 1~10 | 成绩 0~100 |
| 分层 | 界面／业务／数据 | 同 | 同（多一层 JSP） |
| 事务边界 | 开房＝改房态+插单 | 入职＝插员工+插账号 | 录分＝一批全成 |
| 权限 | 三角色菜单 | 三角色菜单 | 三角色 + 数据范围 |
| 验收 | 冒烟断言 | 八条断言 | 行数乘法＋断言 |

  三张表里最值得看的是「事务边界」那一行。
  三个业务完全不同，但「哪些操作必须一起成功」这个判断是一模一样的思路。
  这就是从项目里能带走的东西。
  「权限」那一行也值得看： 前两个项目只做到「菜单按角色」，
  第三个项目多了一层「数据范围」——因为第一次出现了「同一页面、不同数据」。
  这不是设计变复杂了，是需求变复杂了。

  ⑥ 最后：这一课到底学了什么
  --------------------------------------------------------------------
| 层次 | 内容 |
|---|---|
| 语言层 | T-SQL 的查询、函数、流程控制、游标 |
| 对象层 | 表、视图、存储过程、触发器、索引 |
| 机制层 | 事务、锁、隔离级别、权限、备份 |
| 工程层 | 分层、公共类、参数化、断言式验收 |

  四层里，语言层最容易忘（几个月不写就生疏），工程层最难学（要靠项目攒）。
  而中间两层是「一旦懂了就不会忘」的——因为它们讲的是「数据库为什么这样设计」，不是「语法怎么写」。
  所以复习这一课，别背语法。
  翻 14 章的索引、15 章的事务、20 章的那三张骨架表——这些换个数据库、换个语言，都还在。

汇总表里「人数」和「条数」差三倍（4 人 12 条）——因为每人三科。 这两个数经常被混用，混用的结果是「这个班有 12 个人」这种荒谬结论。 排名表里出现了两个第 1（班内排名）， 因为那是一个班里的第一名；全级排名里又出现了两个并列第 4（88 分那两位）。 RANK 给的就是这种「并列同名次、后面跳号」的结果。 三种数据范围那一段，代码只有六行，但它挡住了整个系统的数据泄露。 如果这段写错，学生能看到全级成绩——而页面上一切正常，没有任何报错。

30 秒实验

1. 把汇总里的 DISTINCT 去掉，看「人数」变成几。 会变成人次（12），这就是「人数」「人次」混用的后果。
2. 把 RANK() 换成 ROW_NUMBER()，看并列的两个人变成什么。 会变成 4 和 5，不再并列。
3. 想一想：如果把「三种数据范围」那段删掉，学生登录后会看到什么？ 全级数学成绩。 而页面上看起来完全正常。

常见误区

1. 「一个查询又出汇总又出明细最好」。 两种粒度硬塞一条 SQL，SQL 会难读到没人敢改。 两次查询更清楚。
2. 「COUNT(*) 就够了」。 它数的是行（人次），不是人。 要人数得 COUNT(DISTINCT 学生id)。
3. 「排名用 ROW_NUMBER 更整齐」。 并列会被硬拆成先后，家长会质疑。 成绩排名用 RANK。

全书到这里就完了。 二十章、一百篇、一百个 Demo，从一个库怎么建，到一个完整系统怎么验收。 下次翻这一课时，别从头读——先看 14 章的索引、15 章的事务、20 章的那三张骨架表。

• ☐ （全书完）

相关笔记

• 基本信息数据查询模块设计
• 数据库设计
• 查询进阶的综合用法
• 本章总结
• 48 课 MySQL从入门到精通
• 🪟 SQL Server从入门到精通 · 索引