查找每個部門得分最高的2位員工
# 方法一
SELECT a.*
FROM test AS a
LEFT JOIN test AS a2
ON a.department = a2.department AND a.score <= a2.score
GROUP BY a.name
HAVING COUNT(*) <= 2;
# 方法二
SELECT test.name, test.department, test.score
FROM test
WHERE(SELECT COUNT(*) FROM test as test2
WHERE test2.department = test.department AND test2.score >= test.score
) <= 2
原始數據
# name, department, score
'joe', 'ops', '111'
'aaa', 'ops', '222'
'bbb', 'ops', '333'
'ccc', 'ops', '555'
'ddd', 'dev', '999'
'eee', 'dev', '888'
'fff', 'dev', '777'
'ggg', 'dev', '666'
期望結果:
# name, department, score
'bbb', 'ops', '333'
'ccc', 'ops', '555'
'ddd', 'dev', '999'
'eee', 'dev', '888'
參考鏈接
