牛客SQL每日一题之SQL136 每类试卷得分前3名

SQL136 每类试卷得分前3名

描述

现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002SQLhard602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:31:0078
2100290012021-09-01 09:01:012021-09-01 09:31:0081
3100290022021-09-01 12:01:012021-09-01 12:31:0181
4100390012021-09-01 19:01:012021-09-01 19:40:0186
5100390022021-09-01 12:01:012021-09-01 12:31:5189
6100490012021-09-01 19:01:012021-09-01 19:30:0185
7100590032021-09-01 12:01:012021-09-01 12:31:0285
8100690032021-09-07 10:01:012021-09-07 10:21:0184
9100390032021-09-08 12:01:012021-09-08 12:11:0140
10100390022021-09-01 14:01:01(NULL)(NULL)

找到每类试卷得分的前3名,如果两人最大分数相同,选择最小分数大者,如果还相同,选择uid大者。由示例数据结果输出如下:

tiduidranking
SQL10031
SQL10042
SQL10023
算法10051
算法10062
算法10033

解释:有作答得分记录的试卷tag有SQL和算法,SQL试卷用户1001、1002、1003、1004有作答得分,最高得分分别为81、81、89、85,最低得分分别为78、81、86、40,因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。

示例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

答案

SELECT tid,uid,ranking
FROM (SELECT ei.tag AS tid,uid,
		ROW_NUMBER() OVER(PARTITION BY ei.tag ORDER BY max(er.score) DESC, min(er.score) DESC, er.uid DESC) AS ranking
			FROM exam_record AS er
			LEFT JOIN examination_info AS ei ON ei.exam_id = er.exam_id
			GROUP BY ei.tag,er.uid) AS K
WHERE K.ranking <= 3;

在这里插入图片描述

  • 6
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Redamancy_06

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值