Oracle数据库获取最近三个学期的课程（含重复行）

需求说明 #

使用Oracle数据库，获取最近三个学期的课程数据，并按学期排序。

示例数据 #

semester course userId
10       a       EM-1
8        b       EM-1
9        c       EM-1
9        h       EM-1
2        d       EM-1
2        v       EM-1
2        b       EM-1
3        c       EM-1
3        a       EM-1
3        J       EM-1
3        K       EM-1
3        M       EM-1

期望结果 #

2        d       EM-1
2        v       EM-1
2        b       EM-1
3        c       EM-1
3        a       EM-1
3        J       EM-1
3        K       EM-1
3        M       EM-1
8        b       EM-1

尝试的错误SQL #

SELECT * FROM 
 (
  SELECT semester, course
       , ROW_NUMBER() OVER (ORDER BY course) rno
  FROM emp
 ORDER BY course
 )
WHERE rno <= 3

问题分析与正确解法 #

你的SQL完全偏离需求：用ROW_NUMBER()按课程排序取前3行，既没筛选目标学期，也没按学期排序。

正确SQL（方案一：子查询筛选学期） #

SELECT e.semester, e.course, e.userId
FROM emp e
WHERE e.semester IN (
    SELECT DISTINCT semester
    FROM emp
    ORDER BY semester ASC  -- 按学期升序取前3，对应示例中最小的3个学期：2、3、8
    FETCH FIRST 3 ROWS ONLY
)
ORDER BY e.semester ASC;

正确SQL（方案二：窗口函数标记学期排名） #

SELECT semester, course, userId
FROM (
    SELECT 
        semester, course, userId,
        DENSE_RANK() OVER (ORDER BY semester ASC) semester_rank
    FROM emp
)
WHERE semester_rank <= 3
ORDER BY semester ASC;

说明 #

• 示例中期望的“最近三个学期”是数值最小的3个（2、3、8），所以用ORDER BY semester ASC排序取前3；如果学期数值越大代表越新，只需把ASC改成DESC即可。
• 用DENSE_RANK()可以确保每个不同学期获得唯一排名，避免重复学期干扰；如果确定学期无重复，也可以用ROW_NUMBER()替代。

内容的提问来源于stack exchange，提问作者franco