Oracle RDS中查询学生科目下各试题最新答题记录的SQL问题

先看你的事务表exam_trans结构和数据：

-------------------------------------------------------------------------------------
Subject_id | Question_id | Answerd_id | Student_id | Answer_text | Insert_DTM
-------------------------------------------------------------------------------------
5005       | 3004        | 1004       | 1309       | test        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3004        | NULL       | 1309       | Null        | 2018-05-31 12:09:43
-------------------------------------------------------------------------------------
5005       | 3002        | NULL       | 1309       | Null        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 11:35:47
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       |             | 2018-05-31 11:36:37

你的需求很明确：查询学生1309、科目5005下，每个Question_id对应的最新答题记录，但原来的SQL输出里Question_id=3004出现了两次，不符合预期。

问题出在哪？ #

原来的SQL存在两个关键问题：

1. GROUP BY里写了member_id，但表中实际字段是student_id，属于笔误；
2. 用IN子查询搭配GROUP BY的方式，逻辑不够直观，且在同一问题同一时间有多条记录的场景下，可能会错误保留多条数据。

正确的SQL语句（Oracle RDS适用） #

推荐使用Oracle原生支持的窗口函数ROW_NUMBER()，它能精准定位每个问题的最新记录：

SELECT subject_id, question_id, answerd_id, student_id, answer_text, insert_dtm
FROM (
    SELECT 
        subject_id, 
        question_id, 
        answerd_id, 
        student_id, 
        answer_text, 
        insert_dtm,
        -- 按问题分组，每组内按时间倒序排，最新的记录序号为1
        ROW_NUMBER() OVER (PARTITION BY question_id ORDER BY insert_dtm DESC) AS rn
    FROM exam_trans
    WHERE student_id = 1309 AND subject_id = 5005
) t
WHERE rn = 1
ORDER BY question_id DESC;

逻辑说明 #

1. 内层子查询中，PARTITION BY question_id会把数据按问题ID拆分成分组；
2. ORDER BY insert_dtm DESC让每组内的记录按时间从新到旧排序；
3. ROW_NUMBER()会给每组内的记录分配唯一序号，最新的那条记录序号为1；
4. 外层查询只筛选序号为1的记录，就能保证每个Question_id只展示最新的答题记录。

执行这条SQL后，就能得到你预期的结果。

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