SQL136. 每类试卷得分前3名
描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：| id | exam_id | tag | difficulty | duration | release_time |
| 1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
| id | uid | exam_id | start_time | submit_time | score |
| 1 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
| 2 | 1002 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 3 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
| 4 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
| 5 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
| 6 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
| 7 | 1005 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
| 8 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
| 9 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
| 10 | 1003 | 9002 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
| tid | uid | ranking |
| SQL | 1003 | 1 |
| SQL | 1004 | 2 |
| SQL | 1002 | 3 |
| 算法 | 1005 | 1 |
| 算法 | 1006 | 2 |
| 算法 | 1003 | 3 |
示例1
输入:
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
(9002, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
(9003, '算法', 'medium', 80, '2021-09-01 10:00:00');
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);
输出:
SQL|1003|1 SQL|1004|2 SQL|1002|3 算法|1005|1 算法|1006|2 算法|1003|3
Mysql 解法, 执行用时: 36ms, 内存消耗: 6388KB, 提交时间: 2021-12-27
select tag,uid,rk
from (
select tag, uid, rank() over (partition by tag order by tag,max(score) desc,min(score) desc,uid desc) as rk
from exam_record er left join examination_info ei on er.exam_id = ei.exam_id
group by tag,uid
) as t
where rk<=3
Mysql 解法, 执行用时: 36ms, 内存消耗: 6400KB, 提交时间: 2022-01-25
select tag,uid,ranking
from (
select tag,uid,row_number()over(partition by tag
order by tag,max(score)desc,min(score) desc,uid desc ) as ranking
from exam_record er
left join examination_info ei on er.exam_id=ei.exam_id
group by tag,uid
)test
where ranking <=3
Mysql 解法, 执行用时: 36ms, 内存消耗: 6472KB, 提交时间: 2021-12-19
select tag,uid,ranking
from (
select tag,uid,ROW_NUMBER()OVER(PARTITION BY tag ORDER BY MAX(score) DESC,MIN(score)DESC,uid DESC) ranking
from exam_record er
join examination_info ei using(exam_id)
group by tag,uid
) as t1
where ranking<=3
Mysql 解法, 执行用时: 36ms, 内存消耗: 6504KB, 提交时间: 2022-01-03
WITH t AS (SELECT info.tag tag, er.uid uid, max(er.score) max_score, min(er.score) min_score FROM examination_info info JOIN exam_record er ON info.exam_id = er.exam_id WHERE score IS NOT NULL GROUP BY info.tag, er.uid) SELECT * FROM (SELECT tag, uid, ROW_NUMBER() OVER (PARTITION BY tag ORDER BY max_score DESC, min_score DESC, uid DESC) r_rank FROM t) a WHERE a.r_rank <= 3
Mysql 解法, 执行用时: 36ms, 内存消耗: 6512KB, 提交时间: 2021-12-07
select *
FROM
(select
ei.tag,
er.uid,
ROW_NUMBER() over(partition by ei.tag order by tag,
max(score) desc,
min(score) desc,
er.uid DESC) as rk
from examination_info as ei
join exam_record as er
on ei.exam_id = er.exam_id
group by tag,er.uid) as a
where rk <= 3