SQL(进阶实战02)

最新推荐文章于 2023-03-26 12:04:32 发布
最新推荐文章于 2023-03-26 12:04:32 发布
阅读量468 收藏
点赞数 2
分类专栏： mysql 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44464850/article/details/124612553
版权

1. 分别满足两个活动的人

为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。

现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

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

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602021-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:0081
2100290022021-09-01 12:01:012021-09-01 12:31:0170
3100390012021-09-01 19:01:012021-09-01 19:40:0186
4100390022021-09-01 12:01:012021-09-01 12:31:0189
5100490012021-09-01 19:01:012021-09-01 19:30:0185

示例数据输出结果：

uidactivity
1001activity2
1003activity1
1004activity1
1004activity2

解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2。

示例1
drop table if exists examination_info;
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;


drop table if exists exam_record;
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, 'C++', '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', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(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);
输出

1001|activity2
1003|activity1
1004|activity1
1004|activity2

备注

按用户ID排序输出

题解
思路：
(1)筛选2021年每次试卷得分都大于等于85的人和字符串'activity1'：
1.按照uid进行分组划分，统计每个用户的得分情况。知识点：group by
2.选出提交时间在2021年的试卷。知识点：select...from...where...、year()
3.对于每组要求判断最小得分不小于85。知识点：having、min()

(2)筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串'activity2'：
1.试卷信息和考试信息分布在两个表中，须将其通过exam_id连接起来。知识点：join...on...
2.从连接后的两个表格中满足四个条件的不重复的用户ID，
因为只要求至少一次下述情况(知识点：distinct、where...and...)：
3.提交时间是2021年。year(e_r.submit_time) = 2021
4.试卷难度是困难。e_i.difficulty = 'hard'
5.得分大于80。e_r.score > 80
6.只用了试卷要求时间一半不到的时间就完成。
timestampdiff(minute,start_time,submit_time) * 2 <duration


select uid, activity
from (
select uid,'activity1'as activity
from examination_info t1
inner join 
exam_record t2
on t1.exam_id=t2.exam_id and year(start_time)='2021' 
group by uid
having min(score)>=85
union all
select uid,'activity2'as activity
from examination_info t1
inner join 
exam_record t2
on t1.exam_id=t2.exam_id and year(start_time)='2021' 
where 
 timestampdiff(Minute,start_time,submit_time)<(duration/2)
 and difficulty='hard'
 and score>80
group by uid
)t
order by uid

2. 满足条件的用户的试卷完成数和题目练习数

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号31007算法2020-01-01 10:00:00
21002牛客2号23007算法2020-01-01 10:00:00
31003牛客3号25007算法2020-01-01 10:00:00
41004牛客4号12005算法2020-01-01 10:00:00
51005牛客5号16006C++2020-01-01 10:00:00
61006牛客6号20006C++2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++hard602021-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:0081
2100290022021-09-01 12:01:012021-09-01 12:31:0181
3100390012021-09-01 19:01:012021-09-01 19:40:0186
4100390022021-09-01 12:01:012021-09-01 12:31:5189
5100490012021-09-01 19:01:012021-09-01 19:30:0185
6100590022021-09-01 12:01:012021-09-01 12:31:0285
7100690032021-09-07 10:01:012021-09-07 10:21:0184
8100690012021-09-07 10:01:012021-09-07 10:21:0180

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100480012021-08-02 19:38:0170
6100480022021-08-02 19:48:0190
7100180022021-08-02 19:38:0170
8100480022021-08-02 19:48:0190
9100480022021-08-02 19:58:0194
10100480032021-08-02 19:38:0170
11100480032021-08-02 19:48:0190
12100480032021-08-01 19:38:0180

请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

示例数据输出如下：

uidexam_cntquestion_cnt
100112
100320

解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；1001完成了1次试卷1001，练习了2次题目；1003完成了2次试卷9001、9002，未练习题目（因此计数为0）

示例1
drop table if exists examination_info,user_info,exam_record,practice_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 user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint 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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1004, 8002, '2021-08-02 19:58:01', 94),
(1004, 8003, '2021-08-02 19:38:01', 70),
(1004, 8003, '2021-08-02 19:48:01', 90),
(1004, 8003, '2021-08-01 19:38:01', 80);

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', 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, 9002, '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),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);
输出

1001|1|2
1003|2|0

思路
1.先查出高难度SQL试卷得分平均值大于80并且是7级的红名大佬,统计他们的2021年试卷
SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND YEAR ( submit_time ) = 2021 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
2.2021年试卷总完成次数和题目总练习次数 
left join exam_record,left join  practice_record
题解
方式一：
SELECT DISTINCT
	uid,
	ifnull( t1.exam_cnt, 0 ) AS exam_cnt,
	ifnull( t2.question_cnt, 0 ) AS question_cnt 
FROM
	(
	SELECT
		uid,
		count( exam_id ) exam_cnt 
	FROM
		exam_record 
	WHERE
		YEAR ( submit_time ) = 2021 
		AND uid IN (
		SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND YEAR ( submit_time ) = 2021 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
		) 
	GROUP BY uid 
	) t1
	LEFT JOIN (
	SELECT
		uid,
		count( question_id ) question_cnt 
	FROM
		practice_record 
	WHERE
		YEAR ( submit_time ) = 2021 
		AND uid IN (
		SELECT
			uid 
		FROM examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY uid 
		HAVING avg( score )> 80 
		) 
	GROUP BY uid 
	) t2 USING ( uid ) 
ORDER BY
	exam_cnt,
	question_cnt DESC
	
方式二：
select 
t1.uid,
count(distinct case 
      when year(t2.submit_time) = '2021' then t2.id
      else null end
     ) as exam_cnt, 
count(distinct case 
      when year(t3.submit_time) = '2021' then t3.id
      else null end
     ) as question_cnt 
from	(
		SELECT
			uid 
		FROM
			examination_info
			INNER JOIN exam_record USING ( exam_id )
			INNER JOIN user_info USING ( uid ) 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND score IS NOT NULL 
			AND `level` = 7 
		GROUP BY
			uid 
		HAVING
			avg( score )> 80
		)t1
left join exam_record t2 
on t1.uid = t2.uid 
left join practice_record t3 
on t1.uid = t3.uid 
group by t1.uid
order by exam_cnt asc , question_cnt desc ;

3.每个6/7级用户活跃情况

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号31007算法2020-01-01 10:00:00
21002牛客2号23007算法2020-01-01 10:00:00
31003牛客3号25007算法2020-01-01 10:00:00
41004牛客4号12005算法2020-01-01 10:00:00
51005牛客5号16006C++2020-01-01 10:00:00
61006牛客6号26007C++2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

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

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

uidexam_idstart_timesubmit_timescore
100190012021-09-01 09:01:012021-09-01 09:31:0078
100190012021-09-01 09:01:012021-09-01 09:31:0081
100590012021-09-01 19:01:012021-09-01 19:30:0185
100590022021-09-01 12:01:012021-09-01 12:31:0285
100690032021-09-07 10:01:012021-09-07 10:21:5984
100690012021-09-07 10:01:012021-09-07 10:21:0181
100290012020-09-01 13:01:012020-09-01 13:41:0181
100590012021-09-01 14:01:01(NULL)(NULL)

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

uidquestion_idsubmit_timescore
100180012021-08-02 11:41:0160
100480012021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100180022021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100680022021-08-04 19:58:0194
100680032021-08-03 19:38:0170
100680032021-08-02 19:48:0190
100680032020-08-01 19:38:0180

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：

uidact_month_totalact_days_2021act_days_2021_examact_days_2021_question
10063413
10012211
10051110
10021000
10030000

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

示例1
drop table if exists examination_info,user_info,exam_record,practice_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 user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint 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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2600, 7, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-08-01 19:38:01', 80);

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),
(1005, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '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:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);
备注：

按照总活跃月份数、2021年活跃天数降序排序

思路:
1.获取用户的试卷作答活跃天数、答题活跃天数的数据。
需要拼接exam_record和practice_record的数据，并作出区分

select uid, 
date_format(start_time,'%Y%m') as months,
date_format(start_time,'%Y%m%d') as day_tag,
start_time as days,
'exam' as tag
from exam_record
union all
select uid,
date_format(submit_time,'%Y%m') as months,
date_format(submit_time,'%Y%m%d') as day_tag,
submit_time as days, 
'question' as tag
from practice_record

2.统计2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，
注意需要去重同一天的试卷做题、答题,
count(distinct  case when year(days)=2021 and tag='exam' then day_tag end)
count(distinct  case when year(days)=2021 and tag='question' then day_tag end) as act_days_2021_question

为什么是day_tag，因为day_tag格式化成%Y%m%d。
相当与count(distinct day_tag),去重同一天的试卷做题、答题

3.判断是否是2021年
 case when year(days)=2021 then day_tag end
题解
select uid,
count(distinct months) as act_month_total,
count(distinct  case when year(days)=2021 then day_tag end) as act_days_2021,
count(distinct  case when year(days)=2021 and tag='exam' then day_tag end) as act_days_2021_exam,
count(distinct  case when year(days)=2021 and tag='question' then day_tag end) as act_days_2021_question
from user_info
left join 
(
select 
    uid, 
    date_format(start_time,'%Y%m') as months, 				  	  	
    date_format(start_time,'%Y%m%d') as day_tag,
    start_time as days, 'exam' as tag
from exam_record
union all
select 
    uid, 
    date_format(submit_time,'%Y%m') as months,
    date_format(submit_time,'%Y%m%d') as day_tag,
    submit_time as days, 'question' as tag
from practice_record
)exam
using(uid)
where `level`>=6
group by uid
order by act_month_total desc,act_days_2021 desc

4.每类试卷得分前3名

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

试卷作答记录表：

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);
题解
思路：
1.根据最大分数，最小分数，uid，并排名每类试卷
row_number()over(partition by xxx order by xxxx)
讲解和实战：(3.找出每个学校GPA最低的同学的拓展)
https://blog.csdn.net/weixin_44464850/article/details/124386872


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 
		inner join examination_info 
    using(exam_id)
    group by tag, uid
)ranktable
where ranking <= 3
林园857
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客网-SQL篇-SQL进阶挑战-SQL25 满足条件的用户的试卷完成数和题目练习数
qq_45491551的博客
04-25 404
SQL 问题 找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。 解题 1.找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬。 select u.uid from user_info u join exam_record er on u.uid = er.uid join examination_info ei on er.exam_id = ei.exam_
题解 | #满足条件的用户的试卷完成数和题目练习数#
2301_78234743的博客
09-15 131
一面技术面：30分钟基本纯问项目和实习经历，问得没什么深度，没有八股，总体感觉挺水的二面hr面：一个中年阿姨，问实习经历，公司规模，是否会留实习公司，对海康慧影。不过面试流程推的特别。去年在学校找了个实习，实习到毕业签了入职协议，打听了一下比我早一年实习的学长学姐，他们说是通过实习考核，有毕业证试用期是全额，然后大学同学他们在的公司也是试用期。天津53所，在空港附近，周围比较荒凉线下投的简历，当天投当天面试，面试比较简短，没问什么技术类的东西，就项目情况问了一些表面的东西最后和我说我的方向和他们的需求。
牛客刷题-SQL篇
xfz_0616的博客
07-13 647
聚合函数：1.SQL类别高难度试卷得分的截断平均值； 连接查询：1.满足条件的用户的试卷完成数和题目练习数； 2. 每个6/7级用户活跃情况
SQL(入门实战07)
weixin_44464850的博客
05-02 776
1.统计作答次数 2.得分不小于平均分的最低分 3.平均活跃天数和月活人数 4.月总刷题数和日均刷题数
MySQL数据库进阶实战 完整版PDF.pdf
最新发布
05-07
内容概要：《冬季实战营第三期：MySQL数据库进阶实战》是阿里云云起实验室推出的一个云上学习活动，旨在帮助开发者通过最佳实践快速实践和学习云上技能。本电子手册共包含四个实战章节，涵盖了MySQL数据库的快速部署...
数据库MySQL进阶实战
03-31
总之，通过学习《数据库MySQL进阶实战》，你将能够熟练掌握MySQL的安装、运维管理，以及基本的SQL操作。这将为你在Web开发、数据分析等领域的工作打下坚实的基础，让你能够应对各种数据库相关的实际问题。
MS SQL入门-进阶-实战培训.pdf
10-30
1.3 SQL 管理工具介绍   1.3.1 SQL server配置管理器   1.3.2 SQL server网络配置   1.3.3 连接SQL server服务器   1.3.4 服务器属性配置   1.3.5 命令行下的SQL管理工具   2 设计与管理数据库和对象  ...
linq to sql进阶 教程
07-27
《LINQ to SQL 进阶教程》是一份深入学习LINQ (Language Integrated Query)与SQL交互的宝贵资料，尤其适合已经掌握了基础概念并寻求提升的开发者。LINQ是.NET框架中的一项重要特性，它允许程序员使用一致的查询语法...
MySQL数据库运维管理进阶实战
06-09
本课程“MySQL数据库运维管理进阶实战”旨在提升你对MySQL的深入理解和操作技能，以实现更高效、更安全的数据库管理。 一、MySQL系统变量 系统变量在MySQL中扮演着至关重要的角色，它们控制着数据库的行为和性能。...
牛客SQL135——每个6/7级用户活跃情况
weixin_51350949的博客
03-26 159
思路： 请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。代码合并,使用left join进行连接,但是因为我们在每一个小块里面已经是left join了所以最后大表链接的时候也可以直接用join。本题的难度在于要查找的东西太多了，很难一步写出来，可以先根据每个要求写出对应的sql最后在连接到一个大表里即可。4.2021年试卷作答活跃天数。5.2021年答题活跃天数。3.2021年活跃天数。
SQL题型总结（牛客刷题）
weixin_45366750的博客
09-14 1368
题目参考:https://blog.csdn.net/fashion2014/article/details/78826299?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163132590216780269843900%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=163132590216780269843900&biz_i
SQL 进阶挑战（26 - 30）
村雨遥
06-20 355
N o w C o d e r S Q L 进阶挑战 26 - 30，快 来 和 我 一 起 学 习 吧！
【Kay】SQL窗口函数题目
m0_54115636的博客
03-07 301
一、每类试卷得分前3名 现有试卷信息表examination_info 试卷作答记录表exam_record 请找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者 实例结果： 代码如下： SELECT te.tid,uid,ranking FROM( SELECT tag AS tid, uid, ROW_NUMBER() OVER(PARTITION BY tag .
Mysql窗口函数 (知识点梳理+题目解析+面试实战）(四万字长文，一文读懂，建议收藏后食用)
weixin_45532984的博客
12-19 2841
窗口函数作为Mysql 8的新特性以及面试笔试的重点，掌握并且进行来练习是非常有必要的。本文章详细介绍窗口函数的方方面面，包含知识点梳理，简单案例，题目解析和面试实战，阅读需要约一定的时间，建议收藏后食用。其中在解题部分更是包含了题目分析和要求梳理，解体解答和答案总和，题目由易到难，更包含牛客会员类型题目，其中解体解答花费了很大心思。毫不夸张地说，弄懂本篇文章，可以应付基本所有的面试笔试，尤其是数据开发岗和数据分析岗。
SQL(进阶实战04)
weixin_44464850的博客
05-17 664
1.试卷完成数同比2020年的增长率及排名变化 2.对试卷得分做min-max归一化 3.每份试卷每月作答数和截止当月的作答总数 4. 每月及截止当月的答题情况 5.统计有未完成状态的试卷的未完成数和未完成率
SQL33 对试卷得分做min-max归一化
weixin_48744808的博客
02-27 1194
SQL33 对试卷得分做min-max归一化 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）： 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）： 在物理学及统计学数据计算时，有个概念叫min-max标准化，也被称为离差标准化，是对原始数据的线性变
牛客sql_SQL进阶挑战
Today_2018的博客
02-15 897
做题方法： 对于难度较大的题目，建议采用三步走的方法 第一步：看懂题目，了解需求，看一下别人的代码，理解思路 第二步：自己基本上能够把代码写下来，但是还存在一定的问题 第三步：能够完整无错误写出代码 SQL1 插入记录（一） 注解 1、insert into…values 如果要插入多行，指令是 values ,…多行之间用逗号隔开； 2、Auto Increment类型的列，在插入时用null/default关键字补位，在插入时sql会自动计算各行应有的值； 3、interval 时间间隔关键.
mysql牛客刷题（SQL进阶挑战）
ljm_99的博客
04-11 3501
牛客网sql进阶挑战题目整理
Oracle进阶实战：进程与SQL语句详解
2. **SQL语句类型** - SQL语句主要分为三类：数据定义语言(DDL)用于创建、修改和删除数据库对象；数据操纵语言(DML)用于数据的增删改查；数据控制语言(DCL)用于授权和管理数据库权限。 3. **查询操作** - DISTINCT...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值