三份名单长短不同，SQL怎么横着排？先给每组学生找个位置

三份名单长短不同，SQL怎么横着排？先给每组学生找个位置

假设手里有三份学生名单，要放进一张报表里：亚洲一列，欧洲一列，美洲一列。每列按姓名排序，哪份名单先排完，后面的格子就留空。

这听起来不复杂，复制到表格里就能摆出来。但在数据库里，学生原本是一行一行存的。我们先不要找“行转列模板”，看清楚要怎样摆。

下面这道练习来自旧SQL笔记中“按地区整理学生报表”的选题。笔记提到过LeetCode 618，不过官方题页当前需要订阅才能读完整题面，所以这里自行约定表结构和规则，不把它当成官方原题复述。

先不用SQL，把名单排一次

我们使用这6条记录。student_id唯一；姓名和地区都不为空，姓名只用大写英文字母。地区限定为Asia、Europe、America。同名算不同学生，同名时按编号从小到大排。

在空的MySQL 8学习库中执行；有同名表就换库，不要覆盖业务表。

CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
continent ENUM('Asia','Europe','America') NOT NULL
);
INSERT INTO Student VALUES
(30, 'BO', 'Asia'),
(11, 'ADA', 'Europe'),
(9, 'AMY', 'Asia'),
(24, 'CARL', 'America'),
(3, 'AMY', 'Asia'),
(8, 'BEN', 'Europe');

为什么特意写两个AMY？因为我们排的是学生，不是不同姓名的集合。两名同名学生都得占一个位置。

先把地区拆开，各自排序：

Asia Europe America
AMY(id=3) ADA(id=11) CARL(id=24)
AMY(id=9) BEN(id=8)
BO(id=30)

接下来，拿三份名单里的第一人放一行，第二人放下一行。最后应该是：

位置 Asia Europe America
1 AMY ADA CARL
2 AMY BEN NULL
3 BO NULL NULL

这里用SQL的NULL表示没有学生，并不是姓名叫“NULL”，也不是把空格当成姓名存进去。最终查询只返回三个地区列，“位置”暂时用来帮我们看对齐过程。

注意，同一行的三个人没有业务上的联系。第一行并不是同班、同一天入学，或者编号相同，只是各自在本地区排到第一位。

直接JOIN，为什么越拼人越多？

如果先查亚洲名单，再和欧洲名单、美洲名单拼在一起，会不会就得到这张表？

想一下第一位亚洲学生AMY(id=3)。欧洲有ADA、BEN两位，没有额外的匹配条件时，这个AMY会和两人分别拼一次。

AMY(id=3) — ADA
— BEN

另外两个亚洲学生也各拼两次。三列这样做笛卡尔积，一共是3×2×1=6行，而我们要的是3行。一旦某个地区无人，交叉连接甚至可能把整张报表变成空结果。

现在缺的不是一个更巧的连接条件，而是：谁应该和谁放在同一行？

手工排表时，我们其实已经有了答案：各地区的第一人放一起，第二人放一起。那就在查询里把这个“第几人”算出来。

编号要从每个地区重新开始

ROW_NUMBER()能依次给记录编号。这里不能给全表从1排到6，而要在每个地区里分别从1开始。

SELECT student_id, name, continent,
ROW_NUMBER() OVER (
PARTITION BY continent
ORDER BY name, student_id
) AS pos
FROM Student
ORDER BY continent, pos;

PARTITION BY continent把编号范围分开，ORDER BY name, student_id决定每个范围里谁在前面。表中插入的先后顺序不影响这个约定。

运行后，按我们建表时ENUM的地区顺序显示：

id name continent pos
3 AMY Asia 1
9 AMY Asia 2
30 BO Asia 3
11 ADA Europe 1
8 BEN Europe 2
24 CARL America 1

两个AMY的位置不同。这正是我们需要的“第一名学生、第二名学生”，不是考试名次。

如果改成RANK() OVER (PARTITION BY continent ORDER BY name)，两个AMY都会排第1，BO排第3。后面再按位置分组，两个AMY就挤到了同一个格子里。DENSE_RANK虽然不会跳号，也还是让同名学生共用位置，问题并没有解决。

如果给RANK的排序也加上唯一编号，同名学生不再并列，本例可以排对。但这里既然要的是一人一个位置，直接使用ROW_NUMBER更贴近需求。

有了位置，再把一行里的三个格子填出来

把刚才的查询命名为ranked。现在只看pos=1的三条记录，先不急着合并：

原来：
AMY Asia 1
ADA Europe 1
CARL America 1

只填自己的地区列：
Asia Europe America
AMY NULL NULL
NULL ADA NULL
NULL NULL CARL

“只填自己的列”可以用CASE写：当前记录是Asia，就把姓名放进Asia列，否则给NULL。另两列同理。

这时还只是三行稀疏的格子。要合成一行，可以按位置分组，然后每列取出那个非空姓名。

WITH ranked AS (
SELECT student_id, name, continent,
ROW_NUMBER() OVER (
PARTITION BY continent ORDER BY name, student_id
) AS pos
FROM Student
)
SELECT pos,
CASE WHEN continent = 'Asia' THEN name END AS Asia,
CASE WHEN continent = 'Europe' THEN name END AS Europe,
CASE WHEN continent = 'America' THEN name END AS America
FROM ranked
ORDER BY pos, continent;

CASE没有写ELSE，不匹配时返回NULL。同一个位置里，每个地区最多一位学生，所以每列最多只有一个非空值。

MAX会忽略NULL：AMY、NULL、NULL取出AMY；全部是NULL，结果还是NULL。这里用MAX并不是想找姓名最大的学生，而是把刚才散开的三个格子合并起来。

这也说明为什么前面的编号不能错：如果同一地区有两人共用一个位置，MAX只会取其中一个值，不会替我们发现人数丢了。同名时，报表甚至看不出少了一人。

完整查询

WITH ranked AS (
SELECT name, continent,
ROW_NUMBER() OVER (
PARTITION BY continent ORDER BY name, student_id
) AS pos
FROM Student
)
SELECT MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe,
MAX(CASE WHEN continent = 'America' THEN name END) AS America
FROM ranked
GROUP BY pos
ORDER BY pos;

结果就是前面手工排的三行。第二行的America、第三行的Europe和America都补NULL。

这次GROUP BY确实把多条学生记录合并了，但合并的是“排在同一个位置”的记录，不是把一个地区所有学生压成一行。分组的对象一变，报表含义就不同。

还有两个容易忽略的情况：

• 如果只有Europe有人，依然应按欧洲名单生成行，Asia和America整列为NULL。不能把某个指定地区的名单当作报表的唯一主表。
• 如果三个地区都没人，返回零行即可；我们没有要求凭空造一行三个NULL。

输出有多少行？是三个地区人数的最大值。因为最长的名单排到哪里，那里至少还有一个格子非空。这条规律也可以拿来检查有没有漏行，但仅检查行数还不够，姓名和顺序仍可能不对。

这里的三个地区是固定列。增加Africa并不只是往表里加数据，还需要给查询增加对应的CASE和输出列。如果列的种类由用户临时决定，就已经是动态报表的另一个问题了。中文姓名、大小写或重音的排列规则，也要重新约定排序规则，不能拿本例的ASCII顺序代替。

验证程序怎么设计？

参考程序不用窗口函数，也不再写一次条件聚合。它先在Python里准备三个列表，把学生按地区放进去，各自按姓名和编号排序，然后像文章开头那样逐个位置取人，短名单取不到就补NULL。

再把相同的数据交给独立MySQL实例，执行从本文提取的查询，逐行比较三列姓名及顺序。这样比较的是同一份输入经过两种不同组织方式后，能不能得到同一张报表。

专门加入了同名学生、只有一个地区、缺整个地区、空表以及打乱插入顺序的数据。还执行了几种能运行但逻辑错误的SQL：按姓名并列排名、忘记分地区编号、按地区而不是按位置分组、把NULL改成空字符串。每种都要找到具体反例，不能只看“程序跑完了”。

附录的验证程序使用隔离的MySQL 8数据目录，仅通过本机Unix socket连接，结束后关闭自己启动的进程；不连接已有数据库。需要Python 3和MySQL 8的服务端、客户端及运行库。默认路径是本文的WSL实验环境，可通过BLOG_MYSQL_ROOT指定解包目录；不同安装布局还需调整运行库路径。

下面JSON保存为article-steps.json，Python保存为同目录的verify_roster.py，运行python3 verify_roster.py。JSON保留本文各阶段SQL及示例期望值，Python先核对示例，再批量对照。

[
{
"id": "setup",
"sql": "CREATE TABLE Student (\\n student_id INT PRIMARY KEY,\\n name VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,\\n continent ENUM('Asia','Europe','America') NOT NULL\\n);\\nINSERT INTO Student VALUES\\n (30, 'BO', 'Asia'),\\n (11, 'ADA', 'Europe'),\\n (9, 'AMY', 'Asia'),\\n (24, 'CARL', 'America'),\\n (3, 'AMY', 'Asia'),\\n (8, 'BEN', 'Europe');",
"expected": []
},
{
"id": "ranked",
"sql": "SELECT student_id, name, continent,\\n ROW_NUMBER() OVER (\\n PARTITION BY continent\\n ORDER BY name, student_id\\n ) AS pos\\nFROM Student\\nORDER BY continent, pos;",
"expected": [
"3\\tAMY\\tAsia\\t1",
"9\\tAMY\\tAsia\\t2",
"30\\tBO\\tAsia\\t3",
"11\\tADA\\tEurope\\t1",
"8\\tBEN\\tEurope\\t2",
"24\\tCARL\\tAmerica\\t1"
]
},
{
"id": "sparse",
"sql": "WITH ranked AS (\\n SELECT student_id, name, continent,\\n ROW_NUMBER() OVER (\\n PARTITION BY continent ORDER BY name, student_id\\n ) AS pos\\n FROM Student\\n)\\nSELECT pos,\\n CASE WHEN continent = 'Asia' THEN name END AS Asia,\\n CASE WHEN continent = 'Europe' THEN name END AS Europe,\\n CASE WHEN continent = 'America' THEN name END AS America\\nFROM ranked\\nORDER BY pos, continent;",
"expected": [
"1\\tAMY\\tNULL\\tNULL",
"1\\tNULL\\tADA\\tNULL",
"1\\tNULL\\tNULL\\tCARL",
"2\\tAMY\\tNULL\\tNULL",
"2\\tNULL\\tBEN\\tNULL",
"3\\tBO\\tNULL\\tNULL"
]
},
{
"id": "final",
"sql": "WITH ranked AS (\\n SELECT name, continent,\\n ROW_NUMBER() OVER (\\n PARTITION BY continent ORDER BY name, student_id\\n ) AS pos\\n FROM Student\\n)\\nSELECT MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,\\n MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe,\\n MAX(CASE WHEN continent = 'America' THEN name END) AS America\\nFROM ranked\\nGROUP BY pos\\nORDER BY pos;",
"expected": [
"AMY\\tADA\\tCARL",
"AMY\\tBEN\\tNULL",
"BO\\tNULL\\tNULL"
]
}
]

import getpass
import itertools
import json
import os
import random
import subprocess
import tempfile
import time
from datetime import date, timedelta
from pathlib import Path

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='roster-', 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=120)
if result.returncode or result.stderr.strip():
raise RuntimeError(result.stdout + result.stderr)
return result.stdout.splitlines()

def oracle(rows):
lists = [sorted((n, i) for i, n, c in rows if c == region)
for region in ['Asia', 'Europe', 'America']]
return ['\\t'.join(g[p][0] if p < len(g) else 'NULL' for g in lists)
for p in range(max(map(len, lists), default=0))]

def insert(rows):
if not rows:
return ''
return 'INSERT INTO Student VALUES ' + ','.join(
f"({i},'{n}','{c}')" for i, n, c in rows) + ';'

def chunks(lines, prefix):
groups = []
for line in lines:
if line.startswith(prefix):
groups.append([])
else:
assert groups, line
groups[-1].append(line)
return groups

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]
commands = ['CREATE DATABASE blog_roster; USE blog_roster;']
for i, step in enumerate(steps):
commands.extend([f"SELECT '__step_{i}__';", step['sql']])
outputs = chunks(sql('\\n'.join(commands)), '__step_')
assert outputs == [s['expected'] for s in steps], outputs
record['articleSteps'] = [dict(s, actual=a) for s, a in zip(steps, outputs)]
final = next(s['sql'] for s in steps if s['id'] == 'final')
main = [(30, 'BO', 'Asia'), (11, 'ADA', 'Europe'), (9, 'AMY', 'Asia'),
(24, 'CARL', 'America'), (3, 'AMY', 'Asia'), (8, 'BEN', 'Europe')]
cases = [main, [], [(1, 'Z', 'Europe')]]
for states in itertools.product(range(4), repeat=6):
cases.append([(i+1, ['A','A','B','C','A','B'][i], ['Asia','Europe','America'][v-1])
for i, v in enumerate(states) if v])
rng = random.Random(618)
for _ in range(100):
rows = [(i, rng.choice(['A','B','C','D']), rng.choice(['Asia','Europe','America']))
for i in range(1, rng.randint(1, 35))]
rng.shuffle(rows)
cases.append(rows)
commands = ['USE blog_roster;']
for i, rows in enumerate(cases):
commands.extend(['DELETE FROM Student;', insert(rows), f"SELECT '__case_{i}__';", final])
outputs = chunks(sql('\\n'.join(commands)), '__case_')
assert len(outputs) == len(cases)
for i, (got, rows) in enumerate(zip(outputs, cases)):
assert got == oracle(rows), {'case': i, 'input': rows, 'actual': got, 'expected': oracle(rows)}
record['cases'] = len(cases)
record['exhaustiveCases'] = 4**6
record['randomSeed'] = 618
variants = {
'name-rank': final.replace('ROW_NUMBER()', 'RANK()').replace('ORDER BY name, student_id', 'ORDER BY name'),
'dense-name-rank': final.replace('ROW_NUMBER()', 'DENSE_RANK()').replace('ORDER BY name, student_id', 'ORDER BY name'),
'global-number': final.replace('PARTITION BY continent ', ''),
'wrong-group': final.replace('GROUP BY pos\\nORDER BY pos', 'GROUP BY continent\\nORDER BY continent'),
'empty-string': final.replace('THEN name END', "THEN name ELSE '' END"),
'reverse-output': final.replace('ORDER BY pos;', 'ORDER BY pos DESC;')
}
record['mutants'] = []
for name, query in variants.items():
assert query != final
got = sql('USE blog_roster; DELETE FROM Student;' + insert(main) + query)
assert got != oracle(main), name
record['mutants'].append({'name': name, 'input': main, 'actual': got,
'expected': oracle(main), '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('ROSTER_CHECKS_PASSED')

参考：MySQL 8窗口函数说明。本文只验证这里约定的练习，没有在LeetCode提交，也没有测试大规模名单的性能。