没参加考试，为什么也算了一次？
要统计学生参加各科考试的次数，第一反应大概是：考试记录都在表里，按学生和科目分组，数一下就行。
先看一个小例子。我们有三个学生，两门课。7号和42号都叫 Ada，90号叫 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 得到1，90号的两门课也各得到1。学生一次没参加，怎么反而有次数了？
回头看那一行：
42 / Math / NULL / NULL它虽然没有真实匹配，但确实占了连接结果中的一行。COUNT(*)数的就是行数，它没有数错，只是我们拿“结果行数”代替了“真实考试记录数”。
那怎样才能把这两种行分开？
实际匹配成功时，e.student_id有值；补空时，它是 NULL。于是改成数右侧这一列：
7号 Math：e.student_id = [7, 7] COUNT(e.student_id) -> 2 42号 Math：e.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.py：
import 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_PATH=str(package / 'usr/lib/x86_64-linux-gnu')) run = Path(tempfile.mkdtemp(prefix='zero-count-', dir=base)) (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-priv=NULL', '--innodb-buffer-pool-size=67108864', '--innodb-redo-log-capacity=16777216', '--log-error=' + str(run / 'server.log')] init = subprocess.run(args + ['--initialize-insecure'], env=env, capture_output=True, text=True, timeout=90) if init.returncode: raise RuntimeError(init.stderr) client = [str(package / 'usr/bin/mysql'), '--no-defaults', '--protocol=SOCKET', '--socket=' + str(sock), '-u', 'root', '--batch', '--skip-column-names', '--connect-timeout=2'] server = subprocess.Popen(args + ['--skip-networking', '--mysqlx=0', '--socket=' + str(sock), '--pid-file=' + str(run / 'mysql.pid'), '--tmpdir=' + str(run)], env=env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL) record = {'passed': False, 'run': str(run)} def sql(text): result = subprocess.run(client, input=text, env=env, capture_output=True, text=True, timeout=45) 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([f"SELECT '__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, actual=chunk) 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), repeat=len(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(f'INSERT INTO {table} VALUES {values(rows)};') batch.extend([f"SELECT '__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(timeout=30) except subprocess.TimeoutExpired: server.kill() server.wait(timeout=5) record['serverStopped'] = server.poll() is not None record['socketRemoved'] = not sock.exists() (run / 'results.json').write_text(json.dumps(record, indent=2)) print('RESULTS=' + str(run / 'results.json')) assert record['passed'] and record['serverStopped'] and record['socketRemoved'] print('ZERO_COUNT_CHECKS_PASSED')
