现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）： id uid nick_name achievement level job register_time 1 1001 牛客1号 3100 7 算法 2020-01-01 10:00:00 2 1002 牛客2号 2300 7 算法 2020-01-01 10:00:00 3 1003 牛客3号 2500 7 算法 2020-01-01 10:00:00 4 1004 牛客4号 1200 5 算法 2020-01-01 10:00:00 5 1005 牛客5号 1600 6 C++ 2020-01-01 10:00:00 6 1006 牛客6号 2600 7 C++ 2020-01-01 10:00:00 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）： id exam_id tag difficulty duration release_time 1 9001 SQL hard 60 2021-09-01 06:00:00 2 9002 C++ easy 60 2021-09-01 06:00:00 3 9003 算法 medium 80 2021-09-01 10:00:00 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）： uid exam_id start_time submit_time score 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) 题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）： uid question_id submit_time score 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 请统计每个67级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下： uid act_month_total act_days_2021 act_days_2021_exam act_days_2021_question 1006 3 4 1 3 1001 2 2 1 1 1005 1 1 1 0 1002 1 0 0 0 1003 0 0 0 0 解释：67级用户共有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);

输出

1006|3|4|1|3
1001|2|2|1|1
1005|1|1|1|0
1002|1|0|0|0
1003|0|0|0|0

备注:
按照总活跃月份数、2021年活跃天数降序排序
加载中...