蒙自做网站的公司,泉州公司做网站,数据库wordpress,网站开发代理报价表满足条件的用户的试卷完成数和题目练习数_牛客题霸_牛客网
0 问题描述 基于用户信息表user_info、试卷信息表examination_info、试卷作答记录表exam_record、题目练习记录表practice_record#xff0c;筛选出 高难度SQL试卷得分平均值大于80并且是7级的用户#xff0c;统计他…满足条件的用户的试卷完成数和题目练习数_牛客题霸_牛客网
0 问题描述 基于用户信息表user_info、试卷信息表examination_info、试卷作答记录表exam_record、题目练习记录表practice_record筛选出 高难度SQL试卷得分平均值大于80并且是7级的用户统计他们2021年试卷总完成次数和题目总练习次数结果按试卷完成数升序按题目练习数降序。
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);
2 数据分析
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 uidfrom exam_record where uid in (select uid from user_info where level 7 ) and exam_id in (select exam_id from examination_info where tag SQL and difficulty hard)group by uid having sum(score) / count(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.uidorder by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序按题目练习数降序思路分析
step1: 先筛选出 平均值大于80并且是7级用户的uid得到t1step2t1分别与t2、t3关联要用left join因为有些uid可能没做某个试卷或练习也要保留记录step3count(distinct )时以id区分(case when ..then id )不能以exam_id区分因为存在一个uid可能对同一个试卷或练习做过多次。
3 小结
