本文深入解析MySQL中实现‘每个部门工资前三名’的三种语义：按行号取前3人、按竞赛名次（并列不占位）取前三名、取前三种不同工资对应的所有员工。重点说明窗口函数RANK()、DENSE_RANK()、ROW_NUMBER()在并列处理上的差异，强调排序依据（工资+id）对结果稳定性的影响，并指出LIMIT 3或WHERE误用导致跨部门错误截断的问题。
每个部门取前三名，为什么查出五个人？先说清并列工资怎么算
“每个部门按工资取前三名”，看上去就是排序，再拿前面三个。
先别急着写函数。A部门有六个人：
id 工资
1 10000
2 10000
3 9000
4 9000
5 8000
6 7000
如果只要三个人，1、2、3已经够了。可是3和4工资一样，为什么只留下3？如果同工资不能拆开，8000又算不算前三名？
这几个疑问，决定了SQL到底该怎么写。
先按人排队，走到第三个人就停
假设需求很明确：每个部门最多选三个人。同工资按id小的优先。
那就把规则补完整：先按工资从高到低，同工资再按id从小到大。给排好的人依次编号：
id 工资 人的位置
1 10000 1
2 10000 2
3 9000 3 ← 到这里停
4 9000 4
5 8000 5
6 7000 6
这里1和2工资相同，但他们仍然是两个人，要分别占一个位置。最后留下1、2、3。
对应到SQL，就是ROW_NUMBER()。唯一的id把同工资的先后也定下来，才不会让“到底选3还是4”留给没有约定的排序结果。
如果业务不接受按id选人，就得换成它认可的规则，不能由SQL作者替业务决定。
如果按比赛的名次，并列怎么算？
现在换一个需求：同工资名次相同，有两个人并列第一，下一组就从第三名开始。
这时可以想成：一个人前面有几个工资严格更高的人？再加1，就是他的名次。
id 工资 前面工资更高的人数
1 10000 0 → 第1名
2 10000 0 → 第1名
3 9000 2 → 第3名
4 9000 2 → 第3名
5 8000 4 → 第5名
6 7000 5 → 第6名
于是前三名里有两个第一、两个第三，共四个人。这里没有第二名，不需要硬补一个人当第二名。
这种带名次空缺的规则，对应RANK()。窗口里只按工资排名，才能让同工资的人保留并列。
如果要的是前三种工资呢？
再换一个需求：每个部门找出最高的三种不同工资，拿这些工资的人全部保留。
这回先看工资有几档：
10000 → 第1档 → id 1、2
9000 → 第2档 → id 3、4
8000 → 第3档 → id 5
7000 → 第4档 → id 6
拿前三档就是10000、9000、8000，最后得到五个人。这种不留名次空缺的编号，对应DENSE_RANK()。
如果只想得到三个工资数值，而不要员工明细，那又是另一个输出要求。这里保留员工行，不把五个人压成三条工资记录。
所以，返回3行、4行、5行都可能正确。得先知道我们正在回答哪一种需求。
让同一个人同时带上三个编号
下面使用MySQL 8.0窗口函数。请在自己选定的测试数据库中创建示例表；若已有同名表，换一个名字，不要覆盖已有数据。本文假定工资未知的人不参与评选，因此先过滤NULL。
CREATE TABLE Employee (
id INT PRIMARY KEY,
dept CHAR(1) NOT NULL,
salary INT NULL
);
INSERT INTO Employee VALUES
(1,'A',10000),(2,'A',10000),
(3,'A',9000),(4,'A',9000),
(5,'A',8000),(6,'A',7000),
(7,'B',5000),(8,'B',5000),
(9,'A',NULL),(10,'C',NULL);
多放一个B部门，是为了检查编号会不会在新部门重新开始。C部门只有工资未知的员工，按本例规则没有入选者。
先不筛前三，把编号全部查出来：
WITH ranked AS (
SELECT id, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM Employee
WHERE salary IS NOT NULL
)
SELECT id, dept, salary, rn, rk, dr
FROM ranked
ORDER BY dept, salary DESC, id;
其中A部门的实际结果是：
id 工资 rn rk dr
1 10000 1 1 1
2 10000 2 1 1
3 9000 3 3 2
4 9000 4 3 2
5 8000 5 5 3
6 7000 6 6 4
B部门的两个人，rn分别为1、2，rk和dr都是1。因为PARTITION BY dept让每个部门各算自己的编号。它不会把同工资员工合并成一行。MySQL窗口函数语义。
现在保留上面的WITH ranked AS (...)，按需要替换后面的查询。比如每部门最多三个人：
SELECT id, dept, salary
FROM ranked
WHERE rn <= 3
ORDER BY dept, salary DESC, id;
这段要接在同一条语句的CTE后面执行，ranked不是永久建好的表。
按带空缺的竞赛名次取前三，把rn <= 3改成rk <= 3；取前三种工资，则改成dr <= 3。本例中A部门分别返回id 1、2、3；1、2、3、4；1、2、3、4、5。B部门三种规则都返回7、8。
刚才说加id更稳定，怎么排名这里又不加？
看清这个id加在哪里。
ROW_NUMBER要决定同工资的人谁先拿到第3个位置，因此窗口排序里加入唯一id。
RANK、DENSE_RANK要根据工资保留并列。如果窗口排序也变成ORDER BY salary DESC, id，两个人虽然工资相同，完整排序值却不同了：一个是(10000,1)，一个是(10000,2)。他们就不再并列。并列与行号的定义。
想让结果展示时相同工资按id排列，可以把id放在最外层ORDER BY，就像上面的完整查询。给结果定展示顺序，不必改变计算工资名次的规则。
验证程序故意把DENSE_RANK窗口也加上id，A部门便只剩1、2、3。它不是变得“更准确”，而是换了我们要求的并列规则。
为什么不直接LIMIT 3，或者在原来的WHERE里筛？
LIMIT 3限制整份结果的行数，不会为每个部门单独发三个名额。本例有A、B两个符合工资条件的部门，最多三个人的规则应该共返回五个人；全局限制三行会漏掉B部门。
另一个容易尝试的写法是：
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY dept ORDER BY salary DESC, id
) AS rn
FROM Employee
WHERE rn <= 3;
本次MySQL实际返回Unknown column 'rn' in 'where clause'。这层WHERE还不能用稍后计算出来的窗口编号，所以前面多包一层：里面生成编号，外面按编号筛选。
已有的WHERE salary IS NOT NULL则是在排名以前决定哪些人参加。若需求改成“先给所有员工排名，再只展示某一部分员工”，过滤放哪里就需要重新想，不能一律往里搬。
我怎样检查这三种SQL确实按需求返回？
只拿工资全部不同的样例，三种函数很可能查出相同的员工，根本看不出选错了。
所以先用本例在第3个人附近安排并列，同时放不同部门。程序再检查全部同工资、无并列、空表、只有未知工资、人数不足三人，以及打乱插入顺序的数据。
参考答案不调用另一个排名函数，而是在Python里按需求数：行位置；严格更高工资的人数加1；严格更高的不同工资数加1。用这些数去筛，再和MySQL返回的员工id逐个对照。
另外故意删掉PARTITION BY，检查B部门是否消失；故意给工资排名加唯一id，检查并列是否被拆散。这些错误对照比单看一串“查询成功”更有用。
本次实测是隔离MySQL 8.0.46实例，未比较性能，也没有据此保证某种执行计划。前面的中位数文章讲的是找中间的人，这篇则把“前三”的输出规则先问清，再决定怎么编号。
附录：完整验证程序
下面的Python程序适合已有本文配套Linux MySQL二进制包的环境：通过BLOG_MYSQL_ROOT指定解包根目录，下面应有usr/sbin/mysqld和usr/bin/mysql。依赖的动态库也须已准备好；它不是自动下载或安装MySQL的脚本。
程序在自身目录下创建独立临时数据目录，仅使用/dev/shm下的Unix套接字，不监听TCP。启动参数不读取默认配置，结束后关闭本次实例，数据目录保留以供检查。无需拿自己的数据库做试验。
BLOG_MYSQL_ROOT=/你的/MySQL解包根目录 python3 verify_top.py
如果只是验证查询，可以用自己的空白测试库执行前面的建表和完整SQL；不用为了理解三个编号先搭这套服务器启动器。
import getpass
import json
import os
from pathlib import Path
import random
import subprocess as sp
import tempfile
import time
base = Path(__file__).resolve().parent
run = Path(tempfile.mkdtemp(prefix='top-', dir=base))
(run / 'data').mkdir()
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'))
sock = Path('/dev/shm') / (run.name + '.sock')
common = [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')]
sp.run(common + ['--initialize-insecure'], env=env, check=True,
capture_output=True, timeout=90)
server = sp.Popen(common + ['--skip-networking', '--mysqlx=0', '--socket=' + str(sock),
'--pid-file=' + str(run / 'server.pid')], env=env,
stdout=sp.DEVNULL, stderr=sp.DEVNULL)
record = {'passed': False, 'cases': [], 'run': str(run)}
def sql(query, success=True):
p = sp.run([str(package / 'usr/bin/mysql'), '--no-defaults', '--protocol=SOCKET',
'--socket=' + str(sock), '-uroot', '--batch', '--skip-column-names'],
input=query, text=True, env=env, capture_output=True, timeout=10)
if success:
assert p.returncode == 0, p.stderr
return p.stdout.strip().splitlines()
assert p.returncode != 0, 'Expected an invalid query to fail'
return p.stderr
WINDOWS = """WITH ranked AS (
SELECT id, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM lab.Employee
WHERE salary IS NOT NULL
)
"""
def reset(rows):
sql('TRUNCATE lab.Employee;')
if rows:
values = ','.join(f"({i},'{d}',{'NULL' if s is None else s})" for i, d, s in rows)
sql('INSERT INTO lab.Employee VALUES ' + values + ';')
def selected(field, k=3, window=WINDOWS):
# Field/k are fixed internal test values, never untrusted user input.
return [int(x) for x in sql(window +
f'SELECT id FROM ranked WHERE {field}<={k} ORDER BY dept,salary DESC,id;')]
def oracle(rows, field, k):
answer = []
for dept in sorted({d for _, d, _ in rows}):
group = sorted([r for r in rows if r[1] == dept and r[2] is not None],
key=lambda r: (-r[2], r[0]))
for position, (i, _, salary) in enumerate(group, 1):
higher = [r for r in group if r[2] > salary]
number = {'rn': position, 'rk': 1 + len(higher),
'dr': 1 + len({r[2] for r in higher})}[field]
if number <= k:
answer.append(i)
return answer
try:
for attempt in range(200):
assert server.poll() is None, (run / 'server.log').read_text()
try:
record['version'] = sql('SELECT VERSION();')[0]
break
except AssertionError:
if attempt == 199:
raise
time.sleep(.1)
sql('CREATE DATABASE lab; CREATE TABLE lab.Employee ('
'id INT PRIMARY KEY,dept CHAR(1) NOT NULL,salary INT NULL);')
example = [(i, 'A', salary) for i, salary in enumerate(
[10000, 10000, 9000, 9000, 8000, 7000], 1)] + [
(7, 'B', 5000), (8, 'B', 5000), (9, 'A', None), (10, 'C', None)]
reset(example)
record['exampleRanks'] = sql(WINDOWS +
'SELECT id,dept,salary,rn,rk,dr FROM ranked ORDER BY dept,salary DESC,id;')
record['example'] = {f: selected(f) for f in ['rn', 'rk', 'dr']}
assert record['example'] == {'rn': [1, 2, 3, 7, 8],
'rk': [1, 2, 3, 4, 7, 8],
'dr': [1, 2, 3, 4, 5, 7, 8]}
bad = WINDOWS.replace('PARTITION BY dept ', '')
record['withoutPartition'] = selected('rn', window=bad)
assert record['withoutPartition'] == [1, 2, 3]
bad = WINDOWS.replace('ORDER BY salary DESC)', 'ORDER BY salary DESC, id)')
record['idBreaksPeers'] = selected('dr', window=bad)
assert record['idBreaksPeers'] == record['example']['rn']
record['sameLevelWhereError'] = sql('SELECT id,ROW_NUMBER() OVER '
'(PARTITION BY dept ORDER BY salary DESC,id) AS rn '
'FROM lab.Employee WHERE rn<=3;', success=False)
assert 'Unknown column' in record['sameLevelWhereError']
rng = random.Random(20261108)
cases = [[], [(1, 'A', None)], [(1, 'A', 9)],
[(i, 'A', 5) for i in range(1, 7)],
[(i, 'A', i) for i in range(1, 7)]]
for _ in range(12):
rows = [(i, rng.choice('ABC'), rng.choice([None, 0, -1, 5, 5, 9]))
for i in range(1, rng.randrange(2, 25))]
rng.shuffle(rows)
cases.append(rows)
for index, rows in enumerate(cases):
reset(rows)
for k in [0, 1, 3, 10]:
for field in ['rn', 'rk', 'dr']:
assert selected(field, k) == oracle(rows, field, k), (index, k, field)
record['cases'].append({'index': index, 'rows': len(rows), 'passed': True})
record['passed'] = True
finally:
server.terminate()
try:
server.wait(timeout=20)
except sp.TimeoutExpired:
server.kill()
server.wait()
record['serverStopped'] = server.poll() is not None
record['socketRemoved'] = not sock.exists()
(base / 'top-results.json').write_text(json.dumps(record, indent=2))
print(json.dumps(record))
扫一扫
650
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
