筛选昵称规则和试卷规则的作答记录

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	1900	2	算法	2020-01-01 10:00:00
2	1002	牛客2号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客3号♂	2200	5	算法	2020-01-01 10:00:00
4	1004	牛客4号	2500	6	算法	2020-01-01 10:00:00
5	1005	牛客555号	2000	7	C++	2020-01-01 10:00:00
6	1006	666666	3000	6	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	C++	hard	60	2020-01-01 10:00:00
2	9002	c#	hard	80	2020-01-01 10:00:00
3	9003	SQL	medium	70	2020-01-01 10:00:00

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
4	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
5	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
11	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	81
16	1002	9001	2021-09-06 12:01:01	2021-09-06 12:21:01	80
17	1002	9001	2021-09-06 12:01:01	(NULL)	(NULL)
18	1002	9001	2021-09-07 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
12	1002	9002	2020-02-01 12:01:01	2020-02-01 12:31:01	82
13	1002	9002	2020-02-02 12:11:01	2020-02-02 12:31:01	83
9	1003	9001	2021-09-07 10:01:01	2021-09-07 10:31:01	89
8	1003	9003	2021-02-06 12:01:01	(NULL)	(NULL)
10	1004	9002	2021-08-06 12:01:01	(NULL)	(NULL)
14	1005	9001	2021-02-01 11:01:01	2021-02-01 11:31:01	84
15	1006	9001	2021-02-01 11:01:01	2021-09-01 11:31:01	84

找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分，按用户ID、平均分升序排序。由示例数据结果输出如下：

uid	exam_id	avg_score
1002	9001	81
1002	9002	85
1005	9001	84
1006	9001	84

解释：昵称满足条件的用户有1002、1004、1005、1006；

c开头的试卷有9001、9002；

满足上述条件的作答记录中，1002完成9001的得分有81、80，平均分为81（80.5取整四舍五入得81）；

1002完成9002的得分有90、82、83，平均分为85；

示例1

输入:

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', 1900, 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, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'C++', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'c#', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
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-09-01 11:01:01', '2021-09-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|9001|81
1002|9002|85
1005|9001|84
1006|9001|84

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6376KB, 提交时间: 2021-12-01

select u_i.uid as uid, 
       e_r.exam_id as exam_id,
       round(avg(score), 0) as avg_score
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 score is not null 
and tag rlike '^(C|c).*'
and (nick_name rlike '^[0-9]+$'
    or nick_name rlike '^牛客[0-9]+号$' 
)
group by uid, exam_id
order by uid, avg_score

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2022-02-09

select e.uid,e.exam_id,round(avg(score)) as avg_score
from exam_record e 
left join user_info e1 on e.uid=e1.uid
left join examination_info e2 on e.exam_id=e2.exam_id
where (e1.nick_name REGEXP '^[0-9]+$' or e1.nick_name REGEXP '^牛客[0-9]+号$')
and score is not NULL and e2.tag like 'C%' and e2.tag like 'c%'
group by e.uid,e.exam_id
order by e.uid,avg_score

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2021-12-31

# select ui.uid,er.exam_id,round(avg(er.score)) as avg_score
# from exam_record er 
# left join user_info ui on ui.uid=er.uid
# left join examination_info ei on ei.exam_id=er.exam_id 
# where (ui.nick_name rlike '^牛客[0-9]+号$'&nbs***bsp;
#        ui.nick_name rlike '^[0-9]+$') and ei.tag rlike '^(C|c).*' and er.score is not null
# group by ui.uid,er.exam_id
# order by ui.uid,avg_score; 

select uid, exam_id, round(avg(score), 0) as avg_score
from exam_record
group by uid, exam_id
having uid in (select uid from user_info
               where nick_name rlike '^牛客[0-9]+号$'   # 正则表达式
                  or nick_name rlike '^[0-9]+$'
             )
      and exam_id in (select exam_id
                from examination_info
                where tag like 'C%'           # 通配符
                or tag like 'c%'
               )
      and avg_score IS NOT NULL
order by uid, avg_score;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-02-08

select uid, exam_id, round(avg(score), 0) as avg_score
from exam_record join user_info using(uid)
    join examination_info using(exam_id)
where tag like 'c%'
    and (nick_name rlike '^牛客[0-9]+号$' or nick_name rlike '^[0-9]+$')
    and score is not null
group by uid, exam_id
order by uid, avg_score

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2021-12-07

select u_i.uid as uid,
       e_r.exam_id as exam_id,
       round(avg(score), 0) as avg_score
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 score is not null
and tag rlike '^(C|c).*'
and (nick_name rlike '^[0-9]+$'
    or nick_name rlike '^牛客[0-9]+号$')
group by uid,exam_id
order by uid,avg_score

上一题

下一题

详情