没参加考试为什么也算了一次要统计学生参加各科考试的次数第一反应大概是考试记录都在表里按学生和科目分组数一下就行。先看一个小例子。我们有三个学生两门课。7号和42号都叫 Ada90号叫 Lin。考试记录只有四行学号 科目 7 Math 7 Math 7 Net 42 Net7号参加了两次数学、一次网络42号只参加了一次网络90号一次也没参加。如果报表要求每个学生的每门课都要有一行没参加就显示0直接数这四条记录够吗这个问题来自 LeetCode 1280学生们参加各科测试的次数。题目给出学生、科目、考试记录三张表考试记录允许重复结果按学号和科目排序。下面用自己的小样例展开不直接搬整张题目样例。先试着只数已有记录下面的代码在已连接、已选好学习用数据库的 MySQL 会话里执行。三张表都建为临时表只属于当前连接不要断开连接后接着执行后面的步骤。不要拿业务连接做实验避免临时同名表遮住已有表。CREATE TEMPORARY TABLE Students ( student_id INT PRIMARY KEY, student_name VARCHAR(20) NOT NULL ); CREATE TEMPORARY TABLE Subjects ( subject_name VARCHAR(20) PRIMARY KEY ); CREATE TEMPORARY TABLE Examinations ( student_id INT NOT NULL, subject_name VARCHAR(20) NOT NULL ); INSERT INTO Students VALUES (7, Ada), (42, Ada), (90, Lin); INSERT INTO Subjects VALUES (Math), (Net); INSERT INTO Examinations VALUES (7, Math), (7, Math), (7, Net), (42, Net);注意考试表没有用“学号、科目”做唯一键。同一个学生可以参加同一科考试多次这两条 Math 都要数。现在按刚才最容易想到的办法写SELECT student_id, subject_name, COUNT(*) AS attended_exams FROM Examinations GROUP BY student_id, subject_name ORDER BY student_id, subject_name;结果是7 Math 2 7 Net 1 42 Net 1这三个数都对。但42号的 Math 在哪90号又在哪它们根本没有进入分组。COUNT可以数一组里有几行却不会因为我们想显示0就凭空造出一个原本不存在的“90号、Math”分组。所以先别改统计函数。我们先解决更前面的事让报表应该有的六个位置出现。考试表里没有那从哪儿找90号虽然不在考试记录里却在学生表里。Math 虽然没有和90号一起出现却在科目表里。那就拿每个学生分别配上所有科目取7号 - 配Math、Net - 两行 取42号 - 配Math、Net - 两行 取90号 - 配Math、Net - 两行 学生表3行 × 科目表2行 6个位置这个动作对应CROSS JOIN也就是交叉连接SELECT s.student_id, s.student_name, sub.subject_name FROM Students AS s CROSS JOIN Subjects AS sub ORDER BY s.student_id, sub.subject_name;现在90号的两门课都在了。这里还没有统计考试只有报表的完整名单。这一步能不能在实际业务里直接照搬要先看规则。本题要求所有学生对应所有科目如果某个学生只选了两门课就应从真实选课关系建立名单而不是把全校所有课程都配给他。名单错了后面连接再正确也会多造出“缺考”。把考试记录往六个位置上接现在来处理7号的 Math。考试表里有两条匹配记录那么这个位置应该接出两行。7号的 Net 有一条就接出一行。轮到42号的 Math找不到记录怎么办这里必须保留名单中的位置否则刚才造好的0次行又丢了。所以这一步用LEFT JOIN。匹配条件要同时看学号和科目SELECT s.student_id, sub.subject_name, e.student_id AS matched_student, e.subject_name AS matched_subject FROM Students AS s CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS e ON e.student_id s.student_id AND e.subject_name sub.subject_name ORDER BY s.student_id, sub.subject_name;把结果里两种不同情况抽出来看位置7号 / Math 找到第1条考试 - 7 / Math 找到第2条考试 - 7 / Math 所以得到两行真实匹配 位置42号 / Math 没有找到考试 保留这个位置 - NULL / NULL 所以得到一行补空结果这里的 NULL 是右侧补出来的。并不是考试表真的存了一条“学号NULL、科目NULL”的考试记录。MySQL 对未匹配的左连接行采用这种补空方式见 JOIN 文档。现在有个小问题刚才我们需要保留的那一行接下来会不会也被当成考试为什么 COUNT 星号会把缺考算成1先故意用COUNT(*)数连接结果SELECT s.student_id, s.student_name, sub.subject_name, COUNT(*) AS attended_exams FROM Students AS s CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS e ON e.student_id s.student_id AND e.subject_name sub.subject_name GROUP BY s.student_id, s.student_name, sub.subject_name ORDER BY s.student_id, sub.subject_name;42号的 Math 得到190号的两门课也各得到1。学生一次没参加怎么反而有次数了回头看那一行42 / Math / NULL / NULL它虽然没有真实匹配但确实占了连接结果中的一行。COUNT(*)数的就是行数它没有数错只是我们拿“结果行数”代替了“真实考试记录数”。那怎样才能把这两种行分开实际匹配成功时e.student_id有值补空时它是 NULL。于是改成数右侧这一列7号 Mathe.student_id [7, 7] COUNT(e.student_id) - 2 42号 Mathe.student_id [NULL] COUNT(e.student_id) - 0COUNT(表达式)不统计表达式为 NULL 的行。规则可查 MySQL 聚合函数文档。本例匹配上的右侧学号一定非空可以用它区分匹配与补空一般业务里不要随便换成一列允许为空的备注那会漏掉真实记录。完整答案只需要把统计表达式改过来SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.student_id) AS attended_exams FROM Students AS s CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS e ON e.student_id s.student_id AND e.subject_name sub.subject_name GROUP BY s.student_id, s.student_name, sub.subject_name ORDER BY s.student_id, sub.subject_name;得到六行7 Ada Math 2 7 Ada Net 1 42 Ada Math 0 42 Ada Net 1 90 Lin Math 0 90 Lin Net 0这里不用再给COUNT套IFNULL(..., 0)。该位置已经有一组结果数不到非 NULL 值时COUNT(e.student_id)自己就是0。而且不能改成COUNT(DISTINCT e.student_id)7号数学确实参加两次去重以后两个7只剩一个次数就错了。为什么还要带着学号分组我们把7号和42号故意设为同名。若只按姓名和科目分组两个人的 Net 会合成同一组变成 Ada 参加了两次网络考试。数据库不会知道这两个 Ada 是不同的人除非我们用学号把他们分开。因此这里按学生身份和科目分组不按“看起来一样的名字”认人。查询中同时列出姓名方便核对输出也不依赖省略分组列的数据库规则。同样连接条件缺少科目也会串账若只按学号匹配7号的三个考试记录会同时接到 Math 和 Net 两个位置上。走到这里整条查询各部分都有了来由先确定所有应该出现的位置左连接保留没参加的组合再数真正匹配到的右侧记录。不是先背三种 SQL 语法再猜应该把它们放在哪里。附这道题怎么验证本次在 Ubuntu WSL 的独立 MySQL 8.0.46 实例中运行不用 SQLite 代替 MySQL也不连接已有业务数据库。先按文章顺序执行全部 SQL核对每个中间结果而不只看最终六行。然后做两类检查自动枚举0至2个学生、0至2门科目每个组合参加0、1、2次考试Python 先按输入记录计数再逐个学生、逐门科目生成预期结果与实际 SQL 对照。另加上正文的三人样例。故意改错数补空行、去重考试次数、换成内连接、少一个科目匹配条件、过滤补空行、把同名学生合并。每个错误都必须被正文样例识别不能让一份全是“每人每科考一次”的数据假装验证充分。这里的预期算法不使用 SQL 连接只是在 Python 字典里记次数再循环生成名单。它和被检查的查询走不同路径减少两边一起写错同一个连接条件的机会。完整验证脚本和步骤数据如下。它需要现有 Linux MySQL 8.0 二进制及对应运行库默认路径对应本地实验包其他环境通过BLOG_MYSQL_ROOT指向同样包含usr/bin/mysql、usr/sbin/mysqld的解包目录。不是让你连接生产实例也不是在电脑上再安装一套系统服务。把两份文件放在同一个专用实验目录执行python3 verify_counts.py。脚本在该目录下新建数据目录用随机名字的 Unix socket 通信关闭 TCP 与 MySQL X 监听。新实例密码为空仅用于这次隔离测试不要拿这个配置部署服务器。结束后关闭实例日志保留以便查错。article-steps.json保存的正是上面各段 SQL 与逐步预期[ { id: setup, sql: CREATE TEMPORARY TABLE Students (\n student_id INT PRIMARY KEY,\n student_name VARCHAR(20) NOT NULL\n);\nCREATE TEMPORARY TABLE Subjects (\n subject_name VARCHAR(20) PRIMARY KEY\n);\nCREATE TEMPORARY TABLE Examinations (\n student_id INT NOT NULL,\n subject_name VARCHAR(20) NOT NULL\n);\nINSERT INTO Students VALUES (7, Ada), (42, Ada), (90, Lin);\nINSERT INTO Subjects VALUES (Math), (Net);\nINSERT INTO Examinations VALUES\n (7, Math), (7, Math), (7, Net), (42, Net);, expected: [] }, { id: existing, sql: SELECT student_id, subject_name, COUNT(*) AS attended_exams\nFROM Examinations\nGROUP BY student_id, subject_name\nORDER BY student_id, subject_name;, expected: [ 7\tMath\t2, 7\tNet\t1, 42\tNet\t1 ] }, { id: grid, sql: SELECT s.student_id, s.student_name, sub.subject_name\nFROM Students AS s\nCROSS JOIN Subjects AS sub\nORDER BY s.student_id, sub.subject_name;, expected: [ 7\tAda\tMath, 7\tAda\tNet, 42\tAda\tMath, 42\tAda\tNet, 90\tLin\tMath, 90\tLin\tNet ] }, { id: joined, sql: SELECT s.student_id, sub.subject_name,\n e.student_id AS matched_student, e.subject_name AS matched_subject\nFROM Students AS s\nCROSS JOIN Subjects AS sub\nLEFT JOIN Examinations AS e\n ON e.student_id s.student_id\n AND e.subject_name sub.subject_name\nORDER BY s.student_id, sub.subject_name;, expected: [ 7\tMath\t7\tMath, 7\tMath\t7\tMath, 7\tNet\t7\tNet, 42\tMath\tNULL\tNULL, 42\tNet\t42\tNet, 90\tMath\tNULL\tNULL, 90\tNet\tNULL\tNULL ] }, { id: wrong-count, sql: SELECT s.student_id, s.student_name, sub.subject_name,\n COUNT(*) AS attended_exams\nFROM Students AS s\nCROSS JOIN Subjects AS sub\nLEFT JOIN Examinations AS e\n ON e.student_id s.student_id\n AND e.subject_name sub.subject_name\nGROUP BY s.student_id, s.student_name, sub.subject_name\nORDER BY s.student_id, sub.subject_name;, expected: [ 7\tAda\tMath\t2, 7\tAda\tNet\t1, 42\tAda\tMath\t1, 42\tAda\tNet\t1, 90\tLin\tMath\t1, 90\tLin\tNet\t1 ] }, { id: final, sql: SELECT s.student_id, s.student_name, sub.subject_name,\n COUNT(e.student_id) AS attended_exams\nFROM Students AS s\nCROSS JOIN Subjects AS sub\nLEFT JOIN Examinations AS e\n ON e.student_id s.student_id\n AND e.subject_name sub.subject_name\nGROUP BY s.student_id, s.student_name, sub.subject_name\nORDER BY s.student_id, sub.subject_name;, expected: [ 7\tAda\tMath\t2, 7\tAda\tNet\t1, 42\tAda\tMath\t0, 42\tAda\tNet\t1, 90\tLin\tMath\t0, 90\tLin\tNet\t0 ] } ]verify_counts.pyimport getpass import itertools import json import os from collections import Counter from pathlib import Path import subprocess import sys import tempfile import time base Path(__file__).resolve().parent steps json.loads((base / article-steps.json).read_text()) package Path(os.environ.get(BLOG_MYSQL_ROOT, /mnt/d/blog-ops/experiments/2026-10-03-candidates/mysql-packages/root)) env dict(os.environ, LD_LIBRARY_PATHstr(package / usr/lib/x86_64-linux-gnu)) run Path(tempfile.mkdtemp(prefixzero-count-, dirbase)) (run / data).mkdir() sock Path(/dev/shm) / (run.name .sock) args [str(package / usr/sbin/mysqld), --no-defaults, --basedir str(package / usr), --datadir str(run / data), --lc-messages-dir str(package / usr/share/mysql), --user getpass.getuser(), --secure-file-privNULL, --innodb-buffer-pool-size67108864, --innodb-redo-log-capacity16777216, --log-error str(run / server.log)] init subprocess.run(args [--initialize-insecure], envenv, capture_outputTrue, textTrue, timeout90) if init.returncode: raise RuntimeError(init.stderr) client [str(package / usr/bin/mysql), --no-defaults, --protocolSOCKET, --socket str(sock), -u, root, --batch, --skip-column-names, --connect-timeout2] server subprocess.Popen(args [--skip-networking, --mysqlx0, --socket str(sock), --pid-file str(run / mysql.pid), --tmpdir str(run)], envenv, stdoutsubprocess.DEVNULL, stderrsubprocess.DEVNULL) record {passed: False, run: str(run)} def sql(text): result subprocess.run(client, inputtext, envenv, capture_outputTrue, textTrue, timeout45) if result.returncode or result.stderr.strip(): raise RuntimeError(result.stdout result.stderr) return result.stdout.splitlines() def oracle(students, subjects, exams): counts Counter(exams) return [f{sid}\t{name}\t{subject}\t{counts[sid, subject]} for sid, name in sorted(students) for subject in sorted(subjects)] def values(rows): # Test inputs below are fixed ASCII identifiers, never external SQL input. return ,.join(( ,.join(str(v) if isinstance(v, int) else v for v in row) ) for row in rows) try: for _ in range(200): if server.poll() is not None: raise RuntimeError((run / server.log).read_text()) if sock.exists(): try: sql(SELECT 1;) break except RuntimeError: pass time.sleep(0.1) else: raise TimeoutError(MySQL startup timed out) record[version] sql(SELECT VERSION();)[0] statements [CREATE DATABASE blog_zero_count; USE blog_zero_count;] expected [] for index, step in enumerate(steps): statements.extend([fSELECT __step_{index}__;, step[sql]]) expected.append(step[expected]) actual sql(\n.join(statements)) chunks [] for line in actual: if line.startswith(__step_): chunks.append([]) else: chunks[-1].append(line) assert chunks expected, (chunks, expected) record[articleSteps] [dict(step, actualchunk) for step, chunk in zip(steps, chunks)] setup steps[0][sql].split(INSERT INTO)[0] final next(s[sql] for s in steps if s[id] final) mutants { count-placeholder: final.replace(COUNT(e.student_id), COUNT(*)), deduplicate-attendance: final.replace(COUNT(e.student_id), COUNT(DISTINCT e.student_id)), lose-zero-pairs: final.replace(LEFT JOIN, INNER JOIN), join-only-student: final.replace(AND e.subject_name sub.subject_name, ), filter-null-after-join: final.replace(GROUP BY, WHERE e.student_id IS NOT NULL\nGROUP BY), merge-same-name: final.replace(SELECT s.student_id,, SELECT MIN(s.student_id),) .replace(s.student_id, s.student_name, sub.subject_name\nORDER BY, s.student_name, sub.subject_name\nORDER BY) .replace(ORDER BY s.student_id,, ORDER BY 1,) } fixed_students [(7, Ada), (42, Ada), (90, Lin)] fixed_subjects [Math, Net] fixed_exams [(7, Math), (7, Math), (7, Net), (42, Net)] assert oracle(fixed_students, fixed_subjects, fixed_exams) next( s[expected] for s in steps if s[id] final) cases [(fixed_students, fixed_subjects, fixed_exams)] for n in range(3): for k in range(3): students fixed_students[:n] subjects fixed_subjects[:k] pairs [(sid, subject) for sid, _ in students for subject in subjects] for counts in itertools.product(range(3), repeatlen(pairs)): exams [pair for pair, count in zip(pairs, counts) for _ in range(count)] cases.append((students, subjects, exams)) batch [USE blog_zero_count;, setup] wanted [] for index, (students, subjects, exams) in enumerate(cases): batch.append(DELETE FROM Examinations; DELETE FROM Students; DELETE FROM Subjects;) for table, rows in [(Students, students), (Subjects, [(s,) for s in subjects]), (Examinations, exams)]: if rows: batch.append(fINSERT INTO {table} VALUES {values(rows)};) batch.extend([fSELECT __case_{index}__;, final]) wanted.append(oracle(students, subjects, exams)) # Each mysql invocation uses a new connection, so temporary tables are recreated. output sql(\n.join(batch)) observed [] for line in output: if line.startswith(__case_): observed.append([]) else: observed[-1].append(line) assert observed wanted record[cases] len(cases) record[caseDigestInputs] {students: 0..2, subjects: 0..2, attendancePerPair: 0..2, extra: three-student fixed sample} record[mutants] [] for name, query in mutants.items(): assert query ! final rows sql(USE blog_zero_count;\n steps[0][sql] \n query) assert rows ! oracle(fixed_students, fixed_subjects, fixed_exams), name record[mutants].append({name: name, actual: rows, rejected: True}) record[passed] True finally: server.terminate() try: server.wait(timeout30) except subprocess.TimeoutExpired: server.kill() server.wait(timeout5) record[serverStopped] server.poll() is not None record[socketRemoved] not sock.exists() (run / results.json).write_text(json.dumps(record, indent2)) print(RESULTS str(run / results.json)) assert record[passed] and record[serverStopped] and record[socketRemoved] print(ZERO_COUNT_CHECKS_PASSED)