mysql疯狂练习_01

有智青年i

已于 2023-10-24 20:55:18 修改

阅读量234

点赞数 8

分类专栏： MySQL 文章标签： mysql 数据库 1024程序员节

于 2023-10-23 19:27:54 首次发布

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

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1.找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

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

-- 找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。
-- 有得分记录的试卷：
select exam_id
from examination_info
where duration is not null
;
-- 两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者:
select tag,uid,rank() over (partition by tag order by max(score) desc, min(score) desc, uid desc ) rn
from examination_info a
left join exam_record er on a.exam_id = er.exam_id
group by tag, uid;

-- 找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。
select tag,uid,rn
from (
     select tag,uid,rank() over (partition by tag order by max(score) desc, min(score) desc, uid desc ) rn
from examination_info a
left join exam_record er on a.exam_id = er.exam_id
group by tag, uid
         ) as t1
where rn <= 3;

2. 请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、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号', 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);

-- 请统计每个6/7级用户总活跃月份数、
# 2021年活跃天数、2021年试卷作答活跃天数、
# 2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序
-- 这个有点难，看着答案做的，唉。。。差点意思哦
select u_i.uid as uid,
       count(distinct act_month) as act_month_total,
       count(distinct case
             when year(act_time) = 2021
             then act_day
             end) as act_days_2021,
       count(distinct case
             when year(act_time) = 2021
             and tag = 'exam'
             then act_day
             end) as act_days_2021_exam,
        count(distinct case
             when year(act_time) = 2021
             and tag = 'question'
             then act_day
             end) as act_days_2021_question
from user_info u_i
left join (select uid,
             start_time as act_time,
             date_format(start_time, '%Y%m') as act_month,
             date_format(start_time, '%Y%m%d') as act_day,
             'exam' as tag
      from exam_record
      union all
      select uid,
             submit_time as act_time,
             date_format(submit_time, '%Y%m') as act_month,
             date_format(submit_time, '%Y%m%d') as act_day,
             'question' as tag
      from  practice_record
      ) exam_and_practice
on exam_and_practice.uid = u_i.uid
where u_i.level >= 6
group by uid
order by act_month_total desc, act_days_2021 desc

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。

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, '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:51:01', 78),
(1001, 9002, '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:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);
-- 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。
select a.exam_id,duration, start_time,submit_time,
       timestampdiff(minute, start_time, submit_time) as costtime,
       row_number() over (partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc ) as rn1,
       row_number() over (partition by exam_id order by timestampdiff(minute, start_time, submit_time) ) as rn2
from exam_record a
join examination_info ei on a.exam_id = ei.exam_id
where submit_time is not null;


select distinct exam_id, duration, release_time
from(
select exam_id, duration,release_time,
           sum(case when rn1 = 2 then costtime
               when rn2 = 2 then -costtime else 0 end) as sub
from (
     select a.exam_id,duration, start_time,submit_time,release_time,
       timestampdiff(minute, start_time, submit_time) as costtime,
       row_number() over (partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc ) as rn1,
       row_number() over (partition by exam_id order by timestampdiff(minute, start_time, submit_time) ) as rn2
from exam_record a
join examination_info ei on a.exam_id = ei.exam_id
where submit_time is not null ) as t1
group by exam_id
) table2
where sub * 2 >= duration
order by exam_id desc;

3.找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。

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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84),
(1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89),
(1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81),
(1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81),
(1006, 9001, '2021-05-01 12:01:01', null, null),
(1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78),
(1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 81),
(1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 81),
(1001, 9002, '2021-07-01 12:01:01', null, null);
-- 找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。
select *
from exam_record
where exam_record.start_time is not null;
-- 近三个月没有试卷是未完成状态的用户的试卷作答完成数
-- 分析：近：九月份  近三个月 9，8，6  1006
-- 9 8 7 1001 因为7月有未完成试卷过滤掉

-- 最近三个月的uid，start_time,score,以及连续排名
select uid,start_time,score,
       dense_rank() over (partition by uid order by month(start_time) desc ) as recent_months
from exam_record;

select uid,count(score) as exam_complete_cnt
from(
    select uid,start_time,score,
       dense_rank() over (partition by uid order by month(start_time) desc ) as recent_months
from exam_record
        ) as recent_table
where recent_months <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc
;

-- 另一种：
select
uid ,
count(case when score is not null then 1 else null end) as exam_complete_cnt -- 作答完成的次数
from (
select * ,
dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rn -- 按照月份排序
from  exam_record
   )t1 where rn <= 3 -- 最近三个月的，月份不一定要连续的，9，6，5也是最近3个月！
group by uid
having count(case when score is null then 1 else null end) = 0 -- 无未作答的
order by exam_complete_cnt desc ,uid desc;