id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8002	2021-09-01 19:38:01	80

请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。

示例1

输入:

drop table if exists user_info,exam_record,practice_record;
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号', 1000, 2, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '进击的3号', 2200, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 3000, 7, 'C++', '2020-01-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),
(1003, 8002, '2021-09-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9002, '2021-08-06 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81),
(1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83),
(1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1006, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1002, 9001, '2021-09-07 12:01:01', null, null);

输出:

1002|牛客2号|1200

原站题解

-- union
select uid, nick_name, achievement
from(
    select distinct A.uid, nick_name, achievement, 
    max(date_format(start_time,"%y%m")) over(partition by uid order by uid) latest_active
    from (
        select uid,start_time
        from exam_record
    union all
        select uid, submit_time start_time
        from practice_record
    ) A, user_info ui
    where A.uid = ui.uid and substring(nick_name,1,2) = '牛客' and achievement>=1200 and achievement <= 2500
) X
where latest_active = 2109

select uid,nick_name,achievement
from user_info
where nick_name like '牛客%号' and achievement between 1200 and 2500
and uid in (select uid 
            from(select uid,start_time,
                 rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as ranking
                 from exam_record 
                 ) table1
            where ranking=1 and date_format(start_time,'%Y%m')='202109'
             union
            select uid 
            from(select uid,submit_time,
                 rank()over(partition by uid order by date_format(submit_time,'%Y%m') desc) as ranking
                 from practice_record
                 ) table1
            where ranking=1 and date_format(submit_time,'%Y%m')='202109'
          )

select u.uid,nick_name,achievement
from user_info u 
left join exam_record e1 on u.uid=e1.uid
left join practice_record e2 on u.uid=e2.uid
where nick_name like '牛客%号' and achievement between 1200 and 2500
group by u.uid,nick_name,achievement
having date_format(max(e1.submit_time),'%Y-%m')='2021-09' or date_format(max(e2.submit_time),'%Y-%m')='2021-09'

select uid, nick_name, achievement
from(
    (select uid, max(date_format(act_time,'%Y%m')) as last_act_time
    from (
        select uid, start_time as act_time
        from exam_record
        union all
        select uid, submit_time as act_time
        from practice_record
    )t2
    group by uid)t3
    join user_info using(uid)
)
where nick_name like '牛客%号'
    and achievement between 1200 and 2500
    and last_act_time = 202109

select uid,nick_name,achievement
from user_info
where nick_name like '牛客%号' 
and achievement between 1200 and 2500 
and uid in ( 
select distinct uid 
from(
select uid 
from exam_record
group by uid
having max(date_format(start_time,'%Y%m')) = 202109
UNION
select uid 
from practice_record
group by uid
having max(date_format(submit_time,'%Y%m')) = 202109
) t1
)