SQL(进阶实战05)

已于 2022-05-24 14:57:34 修改
阅读量556 收藏
点赞数
分类专栏： mysql 文章标签： sql 数据库 database
于 2022-05-20 09:08:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44464850/article/details/124876508
版权

1. 0级用户高难度试卷的平均用时和平均得分

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001牛客1号100算法2020-01-01 10:00:00
21002牛客2号21006算法2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间），数据如下：

试卷作答记录表，数据如下

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0187
4100190012021-06-02 19:01:012021-06-02 19:32:0020
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290

请输出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。由示例数据结果输出如下：

uidavg_scoreavg_time_took
10013336.7

解释：0级用户有1001，高难度试卷有9001，1001作答9001的记录有3条，分别用时20分钟、未完成（试卷时长60分钟）、30分钟（未满31分钟），分别得分为80分、未完成（0分处理）、20分。因此他的平均用时为110/3=36.7（保留一位小数），平均得分为33分（取整）

示例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号', 10, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-01-01 10:00:00'),
  (9004, '算法', 'medium', 80, '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);
输出

1001|33|36.7

题解
写法一：

select uid,
round(avg(if(score is not null,score,0)),0)avg_score,
round(avg(if(submit_time is not null,
           timestampdiff(minute,start_time,submit_time),duration)
       ),1)avg_time_took

from examination_info
inner join exam_record
using (exam_id)
inner join user_info
using (uid)
where difficulty='hard' and  `level`=0
group by uid

写法二：
select uid,
       round(avg(new_score), 0) as avg_score,
       round(avg(cost_time), 1) as avg_time_took
from(
    select e_r.uid as uid,
           if(score is not null, score, 0) as new_score,
           if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time
    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 level = 0
    and difficulty = 'hard'
    ) new_table
group by uid

2. 筛选限定昵称成就值活跃日期的用户

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号10002算法2020-01-01 10:00:00
21002牛客2号12003算法2020-01-01 10:00:00
31003进击的3号22005算法2020-01-01 10:00:00
41004牛客4号25006算法2020-01-01 10:00:00
51005牛客5号30007C++2020-01-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
3100190022021-02-02 19:01:012021-02-02 19:30:0187
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
6100190022021-09-01 12:01:01(NULL)(NULL)
5100190022021-09-05 19:01:012021-09-05 19:40:0189
11100290012020-01-01 12:01:012020-01-01 12:31:0181
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
7100290022021-05-05 18:01:012021-05-05 18:59:0290
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
8100390032021-02-06 12:01:01(NULL)(NULL)
9100390012021-09-07 10:01:012021-09-07 10:31:0189
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-02-01 11:31:0184

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380022021-09-01 19:38:0180

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

由示例数据结果输出如下：

uidnick_nameachievement
1002牛客2号1200

解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；

1002最近一次试卷区活跃为2021年9月，最近一次题目区活跃为2021年9月；1004最近一次试卷区活跃为2021年8月，题目区未活跃。

因此最终满足条件的只有1002。

示例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

思路
1.昵称以『牛客』开头『号』结尾：WHERE nick_name LIKE "牛客%号"
2.成就值在1200~2500之间：achievement BETWEEN 1200 AND 2500
3.最近一次活跃（答题或作答试卷）在2021年9月：
4.生成用户每次活跃月份：
试卷区活跃情况：SELECT distinct uid, DATE_FORMAT(start_time, "%Y%m") as active_month
练习区活跃情况：SELECT distinct uid, DATE_FORMAT(submit_time, "%Y%m") as active_month
合并活跃情况：UNION
5.按用户分组：GROUP BY uid
6.筛选最近一次活跃月份：HAVING MAX(active_month)="202109"
题解
		select uid,nick_name,achievement
		from user_info 
		where achievement between 1200 and 2500 
		and nick_name like '牛客%号'
		and uid in
		(
		select uid
		from 
			(
			select distinct uid,max(date_format(start_time,"%Y-%m"))
                over(partition by uid 
                     order by date_format(start_time,"%Y-%m") desc)max_month
			from exam_record
			union
			select distinct uid,max(date_format(submit_time,"%Y-%m"))
                over(partition by uid 
                     order by date_format(submit_time,"%Y-%m") desc)max_month
			from practice_record
			)t
		group by uid
		having max(max_month)='2021-09'
		);

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

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号19002算法2020-01-01 10:00:00
21002牛客2号12003算法2020-01-01 10:00:00
31003牛客3号♂22005算法2020-01-01 10:00:00
41004牛客4号25006算法2020-01-01 10:00:00
51005牛客555号20007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001C++hard602020-01-01 10:00:00
29002c#hard802020-01-01 10:00:00
39003SQLmedium702020-01-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
3100190022021-02-02 19:01:012021-02-02 19:30:0187
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
11100290012020-01-01 12:01:012020-01-01 12:31:0181
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
9100390012021-09-07 10:01:012021-09-07 10:31:0189
8100390032021-02-06 12:01:01(NULL)(NULL)
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-09-01 11:31:0184

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

uidexam_idavg_score
1002900181
1002900285
1005900184
1006900184

解释：昵称满足条件的用户有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

题解
写法一：
select t.uid,t.exam_id,t.avg_score
    from user_info
    inner join (
    select uid,exam_id,round(avg(score),0) avg_score
    from examination_info
    inner join exam_record
    using(exam_id)
    where tag like 'C%' and score is not null
    group by uid,exam_id
    )t
    using (uid)
where nick_name like'牛客%' and nick_name like'%号'
    and (nick_name REGEXP '[^0-9.]')=1 
    or (nick_name REGEXP '[^0-9.]')!=1
order by uid,avg_score

写法二：
SELECT uid, exam_id, ROUND(AVG(score),0) avg_score 
FROM exam_record
WHERE 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 RLIKE "^[cC]"
       ) 
    AND score IS NOT NULL
GROUP BY uid, exam_id
ORDER BY uid,avg_score;

写法三：
SELECT uid, exam_id, ROUND(AVG(score), 0) as avg_score
FROM exam_record
WHERE score IS NOT NULL
    and exam_id in (
        SELECT exam_id
        FROM examination_info
        WHERE tag REGEXP "^[cC]"
    )
    and uid in (
        SELECT uid FROM user_info
        WHERE nick_name REGEXP "^牛客[0-9]+号$"
            OR nick_name REGEXP "^[0-9]+$"
    )
GROUP BY uid, exam_id
ORDER BY uid, avg_score;
拓展 (like、rlike、regexp的用法及区别)
正则表达式
1、字符^
意义：表示匹配的字符必须在最前边。
例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。

2、字符$
意义：与^类似，匹配最末的字符。
例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。

3、字符[0-9]
意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。
例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。

4、字符+
意义：匹配+号前面的字符1次及以上。等价于{1,}。
例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。

示例1
select  ("c"REGEXP "^[cC]")

一、like
MySQL提供了两个通配符供 LIKE 使用：百分号%和下划线_。

百分号（%）通配符匹配任何零个或多个字符的字符串。
下划线（_）通配符匹配任何单个字符。

例如：
s%匹配任何字符串以s字符开头，例如sun和six。
se_匹配以 se 开头，后面跟着的任何一个字符，如see和sea


二、rlike操作符
1、模糊查询字段中包含某关键字的信息。
如：查询所有包含“希望”的信息：select * from student where name rlike '希望'
2、模糊查询字段中以某关键字开头的信息。
如：查询所有以“大”开头的信息：select * from student where name not rlike '^大'
3、模糊查询字段中以某关键字结尾的信息。
如：查询所有以“大”结尾的信息：select * from student where name not rlike '大$'
4、模糊匹配或关系，又称分支条件。
如：查询出字段中包含“幸福，幸运，幸好，幸亏”的信息：
select * from student where name  rlike '幸福|幸运|幸好|幸亏'

三、regexp操作符
MySQL允许您使用REGEXP运算符在SQL语句中匹配模式。
以下说明REGEXP WHER子句中运算符的语法：

SELECT 
    column_list
FROM
    table_name
WHERE
    string_column REGEXP pattern;
    
此语句执行 string_column对pattern的模式匹配。
如果string_column匹配的值为WHERE子句中pattern的表达式返回true，否则返回false。
如果string_column或者pattern是NULL，结果是NULL。
除了REGEXP运算符之外，您还可以使用RLIKE运算符，它是运算符的同义词REGEXP。
REGEXP运营商的否定形式是NOT REGEXP。

示例：
SELECT 
    productname
FROM
    products
WHERE
    productname REGEXP '^(A|B|C)'
ORDER BY productname;

4. 根据指定记录是否存在输出不同情况

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号190算法2020-01-01 10:00:00
21002牛客2号12003算法2020-01-01 10:00:00
31003进击的3号220算法2020-01-01 10:00:00
41004牛客4号250算法2020-01-01 10:00:00
51005牛客555号20007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0187
4100190022021-09-01 12:01:01(NULL)(NULL)
5100190032021-09-02 12:01:01(NULL)(NULL)
6100190042021-09-03 12:01:01(NULL)(NULL)
7100290012020-01-01 12:01:012020-01-01 12:31:0199
8100290032020-02-01 12:01:012020-02-01 12:31:0182
9100290032020-02-02 12:11:01(NULL)(NULL)
10100290022021-05-05 18:01:01(NULL)(NULL)
11100290012021-09-06 12:01:01(NULL)(NULL)
12100390032021-02-06 12:01:01(NULL)(NULL)
13100390012021-09-07 10:01:012021-09-07 10:31:0189

请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

由示例数据结果输出如下：

uidincomplete_cntincomplete_rate
100400.000
100310.500
100140.667

解释：0级用户有1001、1003、1004；他们作答试卷数和未完成数分别为：6:4、2:1、0:0；

存在1001这个0级用户未完成试卷数大于2，因此输出这三个用户的未完成数和未完成率（1004未作答过试卷，未完成率默认填0，保留3位小数后是0.000）；

结果按照未完成率升序排序。

附：如果1001不满足『未完成试卷数大于2』，则需要输出1001、1002、1003的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。

示例1
drop table if exists user_info,exam_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 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', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '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 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, 9002, '2021-09-01 12:01:01', null, null),
(1001, 9003, '2021-09-02 12:01:01', null, null),
(1001, 9004, '2021-09-03 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99),
(1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9003, '2020-02-02 12:11:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', null, null),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(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|0|0.000
1003|1|0.500
1001|4|0.667

思路
1.统计每个用户的等级、未完成数、未完成率和总作答数，生成临时表 t_tag_count:
2.右连接试卷作答表和用户信息表：exam_record RIGHT JOIN user_info USING(uid)
3.按用户分组：GROUP BY uid
统计未完成数：COUNT(start_time) - COUNT(submit_time) as incomplete_cnt
统计未完成率：IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0) as incomplete_rate
保留3位小数：ROUND(x, 3)
统计总作答数：COUNT(start_time) as total_cnt
4.当存在0级用户未完成试卷数大于2时：
筛选存在性条件：WHERE EXISTS (SELECT uid FROM t_tag_count WHERE level = 0 AND incomplete_cnt > 2)
5.输出每个0级用户的试卷未完成数和未完成率：
筛选0级用户：level = 0
SELECT uid, incomplete_cnt, incomplete_rate
当不存在0级用户未完成试卷数大于2时：
筛选存在性条件：WHERE NOT EXISTS (SELECT uid FROM t_tag_count WHERE level = 0 AND incomplete_cnt > 2)
6.合并上述结果，条件互斥，so只可能有一个结果集：UNION ALL
题解
方式一：
with t_temp as (
select uid,`level`,
count(start_time)-count(score) incomplete_cnt, -- 未完成数
round(ifnull(1-count(score)/count(start_time),0),3)incomplete_rate, -- 未完成率
count(start_time) total_count -- 作答数
from user_info 
left join exam_record
using(uid)
group by uid
)
select uid,incomplete_cnt,incomplete_rate
from t_temp
where exists(
	select uid from t_temp where `level`=0 and incomplete_cnt>2
)and `level`=0
union all 
select uid,incomplete_cnt,incomplete_rate
from t_temp
where not exists(
	select uid from t_temp where `level`=0 and incomplete_cnt>2
)and `total_count`>0
ORDER BY incomplete_rate;

方式二：
select uid,incomplete_cnt,incomplete_rate
from
(
	select 
		 uid,
		`level`,
		flag,
		incomplete_cnt,
		incomplete_rate,
		last_value(incomplete_cnt)
    	over(order by level desc,incomplete_cnt asc
        rows between unbounded preceding and unbounded following) tag_count
	from
	(
	select
	info.uid,
    `level`,
    record.uid as flag,-- 用户表没有的uid
	count(start_time)-count(score) incomplete_cnt, -- 未完成数
	round(ifnull(1-count(score)/count(start_time),0),3)incomplete_rate -- 未完成率
	from exam_record record
	right join user_info info
	on record.uid=info.uid
	group by info.uid
	)t
)t1
where (level=0 and tag_count>2) or (tag_count<=2 and flag is not null)
order by incomplete_rate asc
拓展 LAST_VALUE() 函数

FIRST_VALUE()函数是一个窗口函数，允许您选择有序行集中的最前一行。(同理)

LAST_VALUE()函数是一个窗口函数，允许您选择有序行集中的最后一行。

以下显示了LAST_VALUE()函数的语法：

LAST_VALUE (expression) OVER (
    [partition_clause]
    [order_clause]
    [frame_clause]
)

LAST_VALUE()函数返回expression有序行集的最后一行的值。

OVER有三个子句：partition_clause，order_clause，和frame_clause。

partition_clause

partition_clause语法如下：

PARTITION BY expr1, expr2, ...

PARTITION BY子句分配结果集成由一个或多个表达式指定多个分区expr1，expr2等LAST_VALUE()函数被独立地施加到每个分区。

order_clause

order_clause语法如下：

ORDER BY  expr1 [ASC|DESC],...

ORDER BY子句指定LAST_VALUE()函数运行的分区中行的逻辑顺序。

frame_clause

frame_clause定义了所述当前分区的所述子集LAST_VALUE()函数应用。有关更多详细信息frame_clause，请查看窗口功能教程。

MySQL LAST_VALUE() 函数示例

让我们设置一个示例表进行演示。

以下是创建overtime表并将数据填充到表中的脚本。

CREATE TABLE overtime (
    employee_name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    hours INT NOT NULL,
     PRIMARY KEY (employee_name , department)
);

INSERT INTO overtime(employee_name, department, hours)
VALUES('Diane Murphy','Accounting',37),
('Mary Patterson','Accounting',74),
('Jeff Firrelli','Accounting',40),
('William Patterson','Finance',58),
('Gerard Bondur','Finance',47),
('Anthony Bow','Finance',66),
('Leslie Jennings','IT',90),
('Leslie Thompson','IT',88),
('Julie Firrelli','Sales',81),
('Steve Patterson','Sales',29),
('Foon Yue Tseng','Sales',65),
('George Vanauf','Marketing',89),
('Loui Bondur','Marketing',49),
('Gerard Hernandez','Marketing',66),
('Pamela Castillo','SCM',96),
('Larry Bott','SCM',100),
('Barry Jones','SCM',65); 
mysql> select * from overtime;
+-------------------+------------+-------+
| employee_name     | department | hours |
+-------------------+------------+-------+
| Anthony Bow       | Finance    |    66 |
| Barry Jones       | SCM        |    65 |
| Diane Murphy      | Accounting |    37 |
| Foon Yue Tseng    | Sales      |    65 |
| George Vanauf     | Marketing  |    89 |
| Gerard Bondur     | Finance    |    47 |
| Gerard Hernandez  | Marketing  |    66 |
| Jeff Firrelli     | Accounting |    40 |
| Julie Firrelli    | Sales      |    81 |
| Larry Bott        | SCM        |   100 |
| Leslie Jennings   | IT         |    90 |
| Leslie Thompson   | IT         |    88 |
| Loui Bondur       | Marketing  |    49 |
| Mary Patterson    | Accounting |    74 |
| Pamela Castillo   | SCM        |    96 |
| Steve Patterson   | Sales      |    29 |
| William Patterson | Finance    |    58 |
+-------------------+------------+-------+
17 rows in set (0.01 sec)
1）MySQL LAST_VALUE()在整个查询结果示例中

以下语句获取员工姓名，加班时间和加班时间最长的员工：

SELECT
    employee_name,
    hours,
    LAST_VALUE(employee_name) OVER (
        ORDER BY hours
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    ) highest_overtime_employee
FROM
    overtime;

输出是：

+-------------------+-------+---------------------------+
| employee_name     | hours | highest_overtime_employee |
+-------------------+-------+---------------------------+
| Steve Patterson   |    29 | Larry Bott                |
| Diane Murphy      |    37 | Larry Bott                |
| Jeff Firrelli     |    40 | Larry Bott                |
| Gerard Bondur     |    47 | Larry Bott                |
| Loui Bondur       |    49 | Larry Bott                |
| William Patterson |    58 | Larry Bott                |
| Barry Jones       |    65 | Larry Bott                |
| Foon Yue Tseng    |    65 | Larry Bott                |
| Anthony Bow       |    66 | Larry Bott                |
| Gerard Hernandez  |    66 | Larry Bott                |
| Mary Patterson    |    74 | Larry Bott                |
| Julie Firrelli    |    81 | Larry Bott                |
| Leslie Thompson   |    88 | Larry Bott                |
| George Vanauf     |    89 | Larry Bott                |
| Leslie Jennings   |    90 | Larry Bott                |
| Pamela Castillo   |    96 | Larry Bott                |
| Larry Bott        |   100 | Larry Bott                |
+-------------------+-------+------------------------

在此示例中，ORDER BY子句将结果集中行的逻辑顺序指定为从低到高的小时。

默认帧规范如下：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这意味着框架从第一行开始，到结果集的当前行结束。

因此，为了获得加班时间最长的员工，我们将框架规格更改为以下内容：

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

这表示框架从第一行开始，到结果集的最后一行结束。

2）MySQL LAST_VALUE()上的分区示例

以下语句查找每个部门加班时间最长的员工：

SELECT
    employee_name,
    department,
    hours,
    LAST_VALUE(employee_name) OVER (
 PARTITION BY department
        ORDER BY hours
        RANGE BETWEEN
     UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
 ) most_overtime_employee
FROM
    overtime;

下图显示了输出：

+-------------------+------------+-------+------------------------+
| employee_name     | department | hours | most_overtime_employee |
+-------------------+------------+-------+------------------------+
| Diane Murphy      | Accounting |    37 | Mary Patterson         |
| Jeff Firrelli     | Accounting |    40 | Mary Patterson         |
| Mary Patterson    | Accounting |    74 | Mary Patterson         |
| Gerard Bondur     | Finance    |    47 | Anthony Bow            |
| William Patterson | Finance    |    58 | Anthony Bow            |
| Anthony Bow       | Finance    |    66 | Anthony Bow            |
| Leslie Thompson   | IT         |    88 | Leslie Jennings        |
| Leslie Jennings   | IT         |    90 | Leslie Jennings        |
| Loui Bondur       | Marketing  |    49 | George Vanauf          |
| Gerard Hernandez  | Marketing  |    66 | George Vanauf          |
| George Vanauf     | Marketing  |    89 | George Vanauf          |
| Steve Patterson   | Sales      |    29 | Julie Firrelli         |
| Foon Yue Tseng    | Sales      |    65 | Julie Firrelli         |
| Julie Firrelli    | Sales      |    81 | Julie Firrelli         |
| Barry Jones       | SCM        |    65 | Larry Bott             |
| Pamela Castillo   | SCM        |    96 | Larry Bott             |
| Larry Bott        | SCM        |   100 | Larry Bott             |
+-------------------+------------+-------+------------------------+
17 rows in set (0.02 sec)

在这个例子中，首先，PARTITION BY子句按部门划分了员工。然后，ORDER BY子句通过加班从低到高命令每个部门的员工。

在这种情况下，帧规范是整个分区。结果，LAST_VALUE()函数选择了每个分区中的最后一行，分区是加班时间最高的员工。

在本教程中，您学习了如何使用MySQL LAST_VALUE()函数获取有序行集中的最后一行。

林园857
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL39 筛选昵称规则和试卷规则的作答记录
weixin_48744808的博客
03-11 368
SQL39 筛选昵称规则和试卷规则的作答记录 描述 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）： 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）： 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, st
MySQL数据库进阶实战 完整版PDF.pdf
最新发布
05-07
内容概要：《冬季实战营第三期：MySQL数据库进阶实战》是阿里云云起实验室推出的一个云上学习活动，旨在帮助开发者通过最佳实践快速实践和学习云上技能。本电子手册共包含四个实战章节，涵盖了MySQL数据库的快速部署...
SQL 进阶挑战（21 - 25）
村雨遥
06-19 781
N o w C o d e r S Q L 进阶挑战 21 - 25，快 来 和 我 一 起 学 习 吧！
PTA—sql补题（4）
夏日漱石的博客
11-14 738
sql,牛客网题目
数据库系统原理与应用教程（068）—— MySQL 练习题：操作题 90-94（十二）：DML 语句练习
睿思达DBA
07-29 728
练习题
牛客刷题-SQL篇
xfz_0616的博客
07-13 651
聚合函数：1.SQL类别高难度试卷得分的截断平均值； 连接查询：1.满足条件的用户的试卷完成数和题目练习数； 2. 每个6/7级用户活跃情况
数据库MySQL进阶实战
03-31
总之，通过学习《数据库MySQL进阶实战》，你将能够熟练掌握MySQL的安装、运维管理，以及基本的SQL操作。这将为你在Web开发、数据分析等领域的工作打下坚实的基础，让你能够应对各种数据库相关的实际问题。
MS SQL入门-进阶-实战培训.pdf
10-30
1.3 SQL 管理工具介绍   1.3.1 SQL server配置管理器   1.3.2 SQL server网络配置   1.3.3 连接SQL server服务器   1.3.4 服务器属性配置   1.3.5 命令行下的SQL管理工具   2 设计与管理数据库和对象  ...
linq to sql进阶 教程
07-27
《LINQ to SQL 进阶教程》是一份深入学习LINQ (Language Integrated Query)与SQL交互的宝贵资料，尤其适合已经掌握了基础概念并寻求提升的开发者。LINQ是.NET框架中的一项重要特性，它允许程序员使用一致的查询语法...
MySQL数据库运维管理进阶实战
06-09
本课程“MySQL数据库运维管理进阶实战”旨在提升你对MySQL的深入理解和操作技能，以实现更高效、更安全的数据库管理。 一、MySQL系统变量 系统变量在MySQL中扮演着至关重要的角色，它们控制着数据库的行为和性能。...
SQL(入门实战06)
weixin_44464850的博客
04-26 2499
1.创建一张新表 2.修改表 3.删除表 4.创建索引 5.删除索引 6. SQL类别高难度试卷得分的截断平均值
【牛客刷题-SQL进阶挑战】NO4.多表查询
IT邦德
06-04 1494
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
牛客网数据库编程题总结
qq_34892310的博客
12-08 1600
牛客网数据库编程总结
Mysql面试题库
t15310904876的博客
02-20 643
SQL插入 SQL 插入记录(一) 描述 现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。 试题信息表examination_info 预计结果: 9003|SQL|hard|90|2021-01-01 00:00:00 执行语句(方法一): replace into examination_info value
SQL知识点--表与索引操作
你隔壁的小王的博客
02-03 888
前文回顾： SQL知识点--插入记录 SQL知识点--更新记录、删除记录 表的创建、修改与删除 CREATE TABLE 语句 CREATE TABLE 语句用于创建数据库中的表 表的创建、修改与删除： 直接创建表： CREATE TABLE [IF NOT EXISTS] tb_name --...
MySQL 嵌套子查询 with子句 from子查询 in子查询 join子查询
我的博客
10-26 8857
with子句、from子查询、join子查询、in子查询，在复杂查询问题中经常会用到这几种方法，以3个由简到难的案例说明这些方法的用法。
【MySQL】【牛客-SQL进阶挑战】06 其他常用操作
guliguliguliguli的博客
08-17 521
MySQL数据库的其他操作：空值处理、LIMIT的使用等......有一定的难度
SQL(进阶实战06)
weixin_44464850的博客
05-24 578
1. 各用户等级的不同得分表现占比 2. 注册当天就完成了试卷的名单第三页 3. 对过长的昵称截取处理 4. 大小写混乱时的筛选统计
SQL高手进阶：性能提升与实战策略
《SQL语言艺术》是一本深度解析和进阶SQL编程的指南，专为中高级SQL开发人员设计。全书共12章，每章围绕SQL语言的关键原则和最佳实践展开，通过实战案例详尽讲解，涵盖了九种经典查询场景及其对性能的影响，旨在帮助...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值