sql - SQL获取分组的最大日期
问题描述
我有一个像这样的事务表......
-------------------------------------------------------------------------------------
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
从这个表中,我必须找出学生和学科 ID 的最新回答行。
我正在使用的 SQL ..
SELECT subject_id,
question_id,
answer_id,
student_id,
answer_text,
insert_dtm
FROM exam_trans
WHERE student_id = 1309
AND subject_id = 5005
AND insert_dtm IN (SELECT Max(insert_dtm)
FROM exam_trans
WHERE student_id = 1309
AND subject_id = 5005
GROUP BY question_id)
GROUP BY subject_id,
question_id,
answer_id,
member_id,
answer_text,
insert_dtm
ORDER BY answer_id,
question_id DESC
但作为输出我得到
-------------------------------------------------------------------------------------
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
3004 在输出中出现两次,但我的预期输出是
Subject_id | Question_id | Answerd_id | Student_id | Answer_text | Insert_DTM
-------------------------------------------------------------------------------------
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
3004 应该只用最近的时间戳来一次....
任何人都可以帮助我使用正确的 SQL...
我在 AWS 中使用 Oracle RDS
解决方案
因为这是更好的用途ROW_NUMBER()
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER (PARTION BY Question_id, Subject_id
ORDER BY Insert_DTM DESC) as rn
FROM exam_trans
WHERE student_id = 1309
AND subject_id = 5005
)
WHERE rn = 1
推荐阅读
- python - TemplateSyntaxError:预期标记“打印语句结束”,得到“r”
- django - Docker - Django - 在 Ubuntu 服务器上切换到主机卷后出现 PermissionError
- python - 从 one-hot 编码数据帧中获得胜率
- html - 如何使用 flexbox 在不移动周围元素的情况下扩展文本
- bash - 如何在 gitlab ci/cd 中使用自定义变量?
- amazon-web-services - 如何保持 S3 存储桶权限公开
- amazon-web-services - 通过快照 ID 创建的 Ebs 卷
- python - 未调用 CBV form_valid() 和 form_invalid() 方法,未将表单保存到数据库
- audio - 如何找出哪个音频样本属于视频中的哪个帧 - 使用 ffmpeg 或其他工具?
- javascript - 使用开始和结束时间打字稿查找时差