mysql_疯狂练习（持续更新）

有智青年i

已于 2023-10-21 17:05:14 修改

阅读量392

点赞数 18

分类专栏： MySQL 文章标签： sql 数据库

于 2023-10-20 17:19:05 首次发布

本文链接：https://blog.csdn.net/m0_64622390/article/details/133950591

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1.day_01

熟能生巧，多多指教

1.请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出

use db_2;
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, '2020-01-01 10:00:00'),
       (9002, 'C++', 'easy', 60, '2020-02-01 10:00:00'),
       (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score)
VALUES (1001, 9001, '2021-07-02 09:01:01', null, null),
       (1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
       (1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
       (1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
       (1002, 9002, '2021-07-06 12:01:01', null, null),
       (1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
       (1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
       (1003, 9001, '2021-09-08 13:01:01', null, null),
       (1003, 9002, '2021-09-08 14:01:01', null, null),
       (1003, 9003, '2021-09-08 15:01:01', null, null),
       (1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
       (1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
       (1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

-- 请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出

/*
 我认为有成绩就算就算是当月完成试卷数所以count(score) >= 3,不过答案是正确的，但是做的不严谨，该反思
 */
select tag,count(tag) as tag_cnt
from examination_info a
join exam_record er on a.exam_id = er.exam_id
where uid in (select uid
            from db_2.exam_record
            group by uid
    having count(score) >= 3)
group by tag
order by tag_cnt desc
;


/*
正确思路：
1.筛选完成了的试卷的记录。知识点：where
2.筛选月均完成数不小于3的用户。知识点：
    2.1按用户分组group by uid；
    2.2.统计当前用户完成试卷总数count(exam_id)；
    2.3.统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, "%Y%m"))；
    2.4分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3 “当月均完成试卷数”不小于3；
3.关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
4.筛选满足条件的用户。知识点：where uid in (...)
5.统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
6.按次数降序输出。知识点：order by tag_cnt desc
 */
select tag, count(tag) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in (
    select uid
    from exam_record
    where submit_time is not null
    group by uid
    having count(exam_id) / count(distinct DATE_FORMAT(start_time, '%Y%m')) >= 3 -- DATE_FORMAT(date，format)函数用于以不同的格式显示日期/时间数据。
)
group by tag
order by tag_cnt desc

2.请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序

drop table if exists examination_info,user_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 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 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号', 2100, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 1500, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1100, 4, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 3000, 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++', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 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:41:01', 70),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9003, '2021-08-01 12:01:01', '2021-08-01 12:21:01', 60),
(1002, 9002, '2021-08-02 12:01:01', '2021-08-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-01 13:01:01', '2021-09-01 13:41:01', 70),
(1003, 9002, '2021-09-08 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 90),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

-- 请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序
-- SQL类别试卷
select exam_id
from examination_info
where tag = 'SQL';
-- sql类型试卷的uid：
select a.uid
from user_info a 
join exam_record er on a.uid = er.uid
where exam_id in (select exam_id
                  from examination_info
                  where tag = 'SQL');


select exam_id,count(distinct a.uid) as uv,round(avg(score),1) as avg_score
from user_info a
join exam_record er on a.uid = er.uid
where level > 5 and exam_id in (
    select exam_id
from examination_info
where tag = 'SQL'
    )
group by exam_id
ORDER BY uv DESC, avg_score ASC
;
/*
 总结：有些细节还是不能够清晰的把握住，继续加油！！
 */

-- 标准答案：
/*
1.获取每张SQL类别试卷发布日期，作为子查询：
    1.1筛选试卷类别：WHERE tag = "SQL"
    1.2获取试卷ID和发布日期：SELECT exam_id, DATE(release_time)
2.筛选发布当天的作答记录：WHERE (exam_id, DATE(start_time)) IN (...)
3.筛选5级以上的用户：AND uid IN (SELECT uid FROM user_info WHERE level > 5)
4.按试卷ID分组：GROUP BY exam_id
5.计算作答人数：count( DISTINCT uid ) AS uv
6.计算平均分（保留1位小数）：ROUND(avg( score ), 1) AS avg_score
 */
SELECT
    exam_id,
    count( DISTINCT uid ) AS uv,
    ROUND(avg( score ), 1) AS avg_score
FROM exam_record
WHERE (exam_id, DATE(start_time)) IN (
    SELECT exam_id, DATE(release_time)
    FROM examination_info WHERE tag = 'SQL'
) AND uid IN ( SELECT uid FROM user_info WHERE `level` > 5 )
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;

3.统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）

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 user_info;
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;

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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 1500, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1100, 4, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 3000, 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++', 'easy', 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:41:01', 79),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9003, '2021-08-01 12:01:01', '2021-08-01 12:21:01', 60),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-09-01 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-01 13:01:01', '2021-09-01 13:41:01', 81),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 90),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89);

-- 统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）
-- SQL类别的试卷exam_id
select exam_id
from examination_info
where tag = 'SQL';
-- SQL 类别，得分大于80
select level,count(level) as level_cnt
from exam_record a
join user_info ui on a.uid = ui.uid
where exam_id in (select exam_id from examination_info where tag = 'SQL')
and score > 80
group by level
order by level desc ;
/*
 这题简单，库库干就完了，不过还是和标准答案有区别
 */
 
-- 标准答案：
# 以每个等级分组，即level作为分组依据，便于计算每个等级的符合条件的人数。知识点：group by
# 对于每个等级，我们只挑选类别为SQL、且得分大于80的不同的用户进行统计人数，相同的用户做了多次只统计一次：
# 上述两个要求加上分组的等级分布在三个表中，我们可以将exam_record表根据exam_id与examination_info表连接在一起，然后将exam_record表根据uid与user_info连接在一起，这样三个表就连结在一起了。知识点：join...on...
# 用where语句判断上述两种情况。知识点：where
# 按照人数的降序，相同情况下等级降序输出。order by level_cnt desc, level desc 知识点：order by
 select level,
       count(distinct u_i.uid) as level_cnt
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
join user_info u_i
on e_r.uid = u_i.uid
where tag = 'SQL'
and score > 80
group by level
order by level_cnt desc, level desc

2.day_02

1. 请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示

drop table if exists practice_record;
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;

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 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),
(1003, 8001, '2021-08-02 19:38:01', 70),
(1003, 8001, '2021-08-02 19:48:01', 90),
(1003, 8002, '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:41:01', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-09-01 12:01:01', null, null);

-- 请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示
select exam_id as tid,count(distinct uid) uv,count(1) pv
from exam_record
group by exam_id

union

select question_id as tid,count(distinct uid ) uv,count(1) pv
from practice_record
group by question_id
ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC
;
/*
 思路差不多
 */


-- 标准答案：

SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,
COUNT(*) pv FROM exam_record
GROUP BY exam_id
UNION
SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,
COUNT(*) pv FROM practice_record
GROUP BY question_id

ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC;

2. 输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

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);

-- 输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。d5
select uid,
       'activity1' as activity
from exam_record
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all
select distinct uid,
       'activity2' as activity
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where year(e_r.submit_time) = 2021
and e_i.difficulty = 'hard'
and e_r.score > 80
and timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
order by uid;

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

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);

-- 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，
-- 统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序
-- 高难度的sql试卷：
select exam_id
from examination_info
where tag = 'SQL'
  and difficulty = 'hard';

-- 平均值大于80并且是七级的人
select a.uid, avg(score)
from exam_record a
         join user_info ui on a.uid = ui.uid
where exam_id in (select exam_id
                  from examination_info
                  where tag = 'SQL'
                    and difficulty = 'hard')
  and level = '7'
group by uid
having avg(score) > 80;

-- 统计这些人2021年的试卷完成次数和题目总练习次数
-- 试卷完成次数
select er.uid, count(er.uid)
from exam_record er
         right join (select a.uid, avg(score)
               from exam_record a
                        join user_info ui on a.uid = ui.uid
               where exam_id in (select exam_id
                                 from examination_info
                                 where tag = 'SQL'
                                   and difficulty = 'hard')
                 and level = '7'
               group by uid
               having avg(score) > 80) b on er.uid = b.uid
group by uid
;
-- 题目总练习次数
select pr.uid,count(pr.uid)
from  (select a.uid, avg(score)
               from exam_record a
                        join user_info ui on a.uid = ui.uid
               where exam_id in (select exam_id
                                 from examination_info
                                 where tag = 'SQL'
                                   and difficulty = 'hard')
                 and level = '7'
               group by uid
               having avg(score) > 80) b
left  join practice_record pr on b.uid = pr.uid
group by uid
;

SELECT temp.uid,
       COUNT(DISTINCT er.id) AS exam_cnt,
       COUNT(DISTINCT pr.id) AS question_cnt
FROM (
         SELECT uid
         FROM exam_record
                  JOIN user_info USING (uid)
                  JOIN examination_info USING (exam_id)
         WHERE tag = 'SQL'
           and difficulty = 'hard'
           and `level` = 7
         GROUP BY uid
         HAVING AVG(score) > 80
     ) AS temp # 高难度SQL试卷得分平均值大于80并且是7级的红名大佬的uid
         LEFT JOIN exam_record er
                   ON temp.uid = er.uid AND YEAR(er.submit_time) = 2021 # 仅保留2021年的提交记录
         LEFT JOIN practice_record pr
                   ON temp.uid = pr.uid AND YEAR(pr.submit_time) = 2021 # 仅保留2021年的提交记录
GROUP BY uid
HAVING exam_cnt > 0 # 仅保留有试卷完成记录的用户
ORDER BY exam_cnt, question_cnt DESC