-- 按分组取出TOP值，是非常常见的业务需求。如：取每门课的前3名
- 创建测试表
CREATE TABLE SC (
id SERIAL PRIMARY KEY,
stdid INT,
clazzid INT,
course VARCHAR,
score INT
);
- 添加一些虚拟数据
INSERT INTO SC (stdid, clazzid, course, score) VALUES (1, 1, 'Eng', 89);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (2, 2, 'Eng', 79);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (3, 1, 'Eng', 69);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (4, 2, 'Eng', 39);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (5, 1, 'Eng', 99);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (1, 1, 'yuwen', 86);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (2, 2, 'yuwen', 76);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (3, 1, 'yuwen', 64);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (4, 2, 'yuwen', 32);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (5, 1, 'yuwen', 91);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (1, 1, 'shuxue', 11);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (2, 2, 'shuxue', 52);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (3, 1, 'shuxue', 55);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (4, 2, 'shuxue', 88);
INSERT INTO SC (stdid, clazzid, course, score) VALUES (5, 1, 'shuxue', 59);
- 四种方法实现 - 这里没有进行性能对比，感兴趣的可以添加大量的虚拟数据进行对比下。
-- 按分组取出TOP值，是非常常见的业务需求。如：取每门课的前3名
-- 方法一：通过窗口函数实现
SELECT *
FROM (SELECT
*,
row_number()
OVER (
PARTITION BY course
ORDER BY score DESC ) AS rn
FROM SC) t
WHERE t.rn < 4;
-- 方法二：通过嵌套子查询方式
SELECT *
FROM SC m_sc
WHERE
(SELECT COUNT(*)
FROM SC sub_sc
WHERE sub_sc.course = m_sc.course AND sub_sc.score >= m_sc.score) <= 3
ORDER BY course, score DESC;
-- 方法三：在子查询中使用score排序，取前3。并应用in关键字确定记录是否符合该子查询。
SELECT *
FROM SC m_sc
WHERE m_sc.id IN
(SELECT id
FROM SC sub_sc
WHERE sub_sc.course = m_sc.course
ORDER BY score DESC LIMIT 3)
ORDER BY course, score DESC;
-- 方法四：使用自关联
SELECT m_sc.*
FROM SC m_sc
INNER JOIN
(SELECT
rankLeft.id,
COUNT(*) AS rankNum
FROM SC rankLeft
INNER JOIN SC rankRight
ON rankLeft.course = rankRight.course AND rankLeft.score <= rankRight.score
GROUP BY rankLeft.id
HAVING COUNT(*) <= 3) sub_sc ON m_sc.id = sub_sc.id
ORDER BY m_sc.course, m_sc.score DESC;
参考链接：
