数据库中取TOP值得几种实现

-- 按分组取出TOP值，是非常常见的业务需求。如：取每门课的前3名

1. 创建测试表

CREATE TABLE SC (
  id      SERIAL PRIMARY KEY,
  stdid   INT,
  clazzid INT,
  course  VARCHAR,
  score   INT
);

2. 添加一些虚拟数据

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);

3. 四种方法实现 - 这里没有进行性能对比，感兴趣的可以添加大量的虚拟数据进行对比下。
   -- 按分组取出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;

参考链接：

1. iSun
2. PostgreSQL窗口函数复习笔记