【牛客刷题-SQL进阶挑战】NO6.其他常用操作

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

在这里插入图片描述

1 🌈 空值处理

🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率

📖 exam_record 表结构
在这里插入图片描述

🚀 题目描述
现有试卷作答记录表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-0209:01:01|2020-01-0209:21:01|80||2|1001|9001|2021-05-0210:01:01|2021-05-0210:30:01|81||3|1001|9001|2021-09-0212:01:01|NULL|NULL|+----+------+---------+---------------------+---------------------+-------+

请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。
由示例数据结果输出如下：
+---------+----------------+-----------------+| exam_id | incomplete_cnt | incomplete_rate |+---------+----------------+-----------------+|9001|1|0.333|+---------+----------------+-----------------+

解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，
因此未完成数为1，未完成率为0.333（保留3位小数）

🚀 建表语句
droptableifexists exam_record;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2020-01-02 09:01:01','2020-01-02 09:21:01',80),(1001,9001,'2021-05-02 10:01:01','2021-05-02 10:30:01',81),(1001,9001,'2021-09-02 12:01:01',null,null);

🍌🍌 答案
select exam_id,sum(casewhen score isnullthen1else0end) incomplete_cnt,round((count(*)-count(submit_time))/count(*),3) incomplete_rate 
from exam_record
groupby exam_id
HAVING(count(*)-count(submit_time))<>0;

在这里插入图片描述

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

📖 user_info 表结构
在这里插入图片描述
📖 examination_info 表结构

📖 exam_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间），数据如下：
+----+------+------------+-------------+-------+--------+---------------------+| id | uid  | nick_name  | achievement |level| job    | register_time       |+----+------+------------+-------------+-------+--------+---------------------+|1|1001| 牛客1号    |10|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号    |2100|6| 算法   |2020-01-0110: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|2020-01-0110:00:00||2|9002|SQL| easy       |60|2020-01-0110:00:00||3|9004| 算法   | medium     |80|2020-01-0110: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-0209:01:01|2020-01-0209:21:59|80||2|1001|9001|2021-05-0210:01:01|NULL|NULL||3|1001|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||4|1001|9001|2021-06-0219:01:01|2021-06-0219:32:00|20||5|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||6|1001|9002|2021-09-0112:01:01|NULL|NULL||7|1002|9002|2021-05-0518:01:01|2021-05-0518:59:02|90|+----+------+---------+---------------------+---------------------+-------+

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

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

🚀 建表语句
droptableifexists examination_info,user_info,exam_record;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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');INSERTINTO 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);

🍌🍌 答案
SELECT
    ui.uid,ROUND(AVG(IFNULL(score,0)),0) avg_score,ROUND(AVG(IFNULL(TIMESTAMPDIFF(minute,start_time,submit_time),ei.duration)),1) avg_time_took
FROM user_info ui
LEFTJOIN exam_record er USING(uid)LEFTJOIN examination_info ei USING(exam_id)WHERElevel=0AND difficulty ='hard'GROUPBY ui.uid;

在这里插入图片描述

2 🌈 高级条件语句

🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率

📖 user_info 表结构
在这里插入图片描述
📖 exam_record 表结构

📖 practice_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1号       |1000|2| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-01-0110:00:00||3|1003| 进击的3号     |2200|5| 算法   |2020-01-0110:00:00||4|1004| 牛客4号       |2500|6| 算法   |2020-01-0110:00:00||5|1005| 牛客5号       |3000|7| C++|2020-01-0110: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-0209:01:01|2020-01-0209:21:59|80||2|1001|9001|2021-05-0210:01:01|NULL|NULL||3|1001|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||4|1001|9001|2021-06-0219:01:01|2021-06-0219:32:00|20||5|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||6|1001|9002|2021-09-0112:01:01|NULL|NULL||7|1002|9002|2021-05-0518:01:01|2021-05-0518:59:02|90||8|1003|9003|2021-02-0612:01:01|NULL|NULL||9|1003|9001|2021-09-0710:01:01|2021-09-0710:31:01|89||10|1004|9002|2021-08-0612:01:01|NULL|NULL||11|1002|9001|2020-01-0112:01:01|2020-01-0112:31:01|81||12|1002|9002|2020-02-0112:01:01|2020-02-0112:31:01|82||13|1002|9002|2020-02-0212:11:01|2020-02-0212:31:01|83||14|1005|9001|2021-02-0111:01:01|2021-02-0111:31:01|84||15|1006|9001|2021-02-0111:01:01|2021-02-0111:31:01|84||16|1002|9001|2021-09-0612:01:01|2021-09-0612:21:01|80||17|1002|9001|2021-09-0612:01:01|NULL|NULL||18|1002|9001|2021-09-0712:01:01|NULL|NULL|+----+------+---------+---------------------+---------------------+-------+

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：
+----+------+-------------+---------------------+-------+| id | uid  | question_id | submit_time         | score |+----+------+-------------+---------------------+-------+|1|1001|8001|2021-08-0211:41:01|60||2|1002|8001|2021-09-0219:30:01|50||3|1002|8001|2021-09-0219:20:01|70||4|1002|8002|2021-09-0219:38:01|70||5|1003|8002|2021-09-0119:38:01|80|+----+------+-------------+---------------------+-------+

请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。
由示例数据结果输出如下：
+------+------------+-------------+| uid  | nick_name  | achievement |+------+------------+-------------+|1002| 牛客2号    |1200|+------+------------+-------------+

解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；
1002最近一次试卷区活跃为2021年9月，最近一次题目区活跃为2021年9月；
1004最近一次试卷区活跃为2021年8月，题目区未活跃。
因此最终满足条件的只有1002。

🚀 建表语句
droptableifexists user_info,exam_record,practice_record;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE practice_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    question_id intNOTNULLCOMMENT'题目ID',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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);INSERTINTO 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);

🍌🍌 答案
select uid, nick_name, achievement
from(selectdistinct A.uid, nick_name, achievement,max(date_format(start_time,"%y%m"))over(partitionby uid orderby uid) latest_active
    from(select uid,start_time
        from exam_record
    unionallselect 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>=1200and achievement <=2500) X
where latest_active =2109;

在这里插入图片描述

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

📖 user_info 表结构
在这里插入图片描述
📖 examination_info 表结构

📖 exam_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|1900|2| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-01-0110:00:00||3|1003| 牛客3号♂      |2200|5| 算法   |2020-01-0110:00:00||4|1004| 牛客4号       |2500|6| 算法   |2020-01-0110:00:00||5|1005| 牛客555号     |2000|7| C++|2020-01-0110:00:00||6|1006|666666|3000|6| C++|2020-01-0110: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-0110:00:00||2|9002| c#   | hard       |       80 | 2020-01-01 10:00:00 ||3|9003|SQL| medium     |70|2020-01-0110: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-0209:01:01|2020-01-0209:21:59|80||2|1001|9001|2021-05-0210:01:01|NULL|NULL||3|1001|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||4|1001|9001|2021-06-0219:01:01|2021-06-0219:32:00|20||5|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||6|1001|9002|2021-09-0112:01:01|NULL|NULL||7|1002|9002|2021-05-0518:01:01|2021-05-0518:59:02|90||8|1003|9003|2021-02-0612:01:01|NULL|NULL||9|1003|9001|2021-09-0710:01:01|2021-09-0710:31:01|89||10|1004|9002|2021-08-0612:01:01|NULL|NULL||11|1002|9001|2020-01-0112:01:01|2020-01-0112:31:01|81||12|1002|9002|2020-02-0112:01:01|2020-02-0112:31:01|82||13|1002|9002|2020-02-0212:11:01|2020-02-0212:31:01|83||14|1005|9001|2021-02-0111:01:01|2021-02-0111:31:01|84||15|1006|9001|2021-09-0111:01:01|2021-09-0111:31:01|84||16|1002|9001|2021-09-0612:01:01|2021-09-0612:21:01|80||17|1002|9001|2021-09-0612:01:01|NULL|NULL||18|1002|9001|2021-09-0712:01:01|NULL|NULL|+----+------+---------+---------------------+---------------------+-------+

找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母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；

🚀 建表语句
droptableifexists examination_info,user_info,exam_record;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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');INSERTINTO 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);

🍌🍌 答案
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 isnotnulland tag rlike'^(C|c).*'and(nick_name rlike'^[0-9]+$'or nick_name rlike'^牛客[0-9]+号$')groupby uid, exam_id
orderby uid, avg_score;

在这里插入图片描述

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

📖 user_info 表结构
在这里插入图片描述
📖 exam_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|19|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-01-0110:00:00||3|1003| 牛客3号♂      |22|0| 算法   |2020-01-0110:00:00||4|1004| 牛客4号       |25|0| 算法   |2020-01-0110:00:00||5|1005| 牛客555号     |2000|7| C++|2020-01-0110:00:00||6|1006|666666|3000|6| C++|2020-01-0110: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-0209:01:01|2020-01-0209:21:59|80||2|1001|9001|2021-05-0210:01:01|NULL|NULL||3|1001|9002|2021-02-0219:01:01|2021-02-0219:30:01|87||4|1001|9002|2021-09-0112:01:01|NULL|NULL||5|1001|9003|2021-09-0212:01:01|NULL|NULL||6|1001|9004|2021-09-0312:01:01|NULL|NULL||7|1002|9001|2020-01-0112:01:01|2020-01-0112:31:01|99||8|1002|9003|2020-02-0112:01:01|2020-02-0112:31:01|82||9|1002|9003|2020-02-0212:11:01|NULL|NULL||10|1002|9002|2021-05-0518:01:01|NULL|NULL||11|1002|9001|2021-09-0612:01:01|NULL|NULL||12|1003|9003|2021-02-0612:01:01|NULL|NULL||13|1003|9001|2021-09-0710:01:01|2021-09-0710:31:01|89|+----+------+---------+---------------------+---------------------+-------+

请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；
若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。
由示例数据结果输出如下：
+------+----------------+-----------------+| uid  | incomplete_cnt | incomplete_rate |+------+----------------+-----------------+|1004|0|0.000||1003|1|0.500||1001|4|0.667|+------+----------------+-----------------+

解释：0级用户有1001、1003、1004；他们作答试卷数和未完成数分别为：6:4、2:1、0:0；
存在1001这个0级用户未完成试卷数大于2，因此输出这三个用户的未完成数和未完成率（1004未作答过试卷，未完成率默认填0，保留3位小数后是0.000）；
结果按照未完成率升序排序。
附：如果1001不满足『未完成试卷数大于2』，则需要输出1001、1002、1003的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。

🚀 建表语句
droptableifexists user_info,exam_record;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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);

🍌🍌 答案
with t_tag_cnt as(select b.uid,`level`,ifnull(count(start_time),0)as complete_cnt
    ,ifnull(count(start_time)-count(score),0)as incomplete_cnt
    ,max(count(start_time)-count(score))over(partitionbylevel)as max_incomplete_cnt
    from exam_record a
    rightjoin user_info b on a.uid = b.uid
    groupby b.uid,`level`)select uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3)as incomplete_rate
from t_tag_cnt where`level`=0and max_incomplete_cnt>2unionselect uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3)as incomplete_rate
from t_tag_cnt wherenotEXists(select uid from t_tag_cnt where`level`=0and max_incomplete_cnt>2)and complete_cnt !=0orderby incomplete_rate;

在这里插入图片描述

🚀 SQL41 各用户等级的不同得分表现占比

📖 user_info 表结构
在这里插入图片描述
📖 exam_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|19|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-01-0110:00:00||3|1003| 牛客3号♂      |22|0| 算法   |2020-01-0110:00:00||4|1004| 牛客4号       |25|0| 算法   |2020-01-0110:00:00||5|1005| 牛客555号     |2000|7| C++|2020-01-0110:00:00||6|1006|666666|3000|6| C++|2020-01-0110: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-0209:01:01|2020-01-0209:21:59|80||2|1001|9001|2021-05-0210:01:01|NULL|NULL||3|1001|9002|2021-02-0219:01:01|2021-02-0219:30:01|75||4|1001|9002|2021-09-0112:01:01|2021-09-0112:11:01|60||5|1001|9003|2021-09-0212:01:01|2021-09-0212:41:01|90||6|1001|9001|2021-06-0219:01:01|2021-06-0219:32:00|20||7|1001|9002|2021-09-0519:01:01|2021-09-0519:40:01|89||8|1001|9004|2021-09-0312:01:01|NULL|NULL||9|1002|9001|2020-01-0112:01:01|2020-01-0112:31:01|99||10|1002|9003|2020-02-0112:01:01|2020-02-0112:31:01|82||11|1002|9003|2020-02-0212:11:01|2020-02-0212:41:01|76|+----+------+---------+---------------------+---------------------+-------+

为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），
请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，
结果按用户等级降序、占比降序排序。
由示例数据结果输出如下：
+-------+-------------+-------+|level| score_grade | ratio |+-------+-------------+-------+|3| 良          |0.667||3| 优          |0.333||0| 良          |0.500||0| 中          |0.167||0| 优          |0.167||0| 差          |0.167|+-------+-------------+-------+

🚀 建表语句
droptableifexists user_info,exam_record;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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');INSERTINTO 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',75),(1001,9002,'2021-09-01 12:01:01','2021-09-01 12:11:01',60),(1001,9003,'2021-09-02 12:01:01','2021-09-02 12:41:01',90),(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,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','2020-02-02 12:41:01',76);

🍌🍌 答案
WITH s1 AS(SELECT exam_record.uid ,level, score,CASEWHEN score<60THEN'差'WHEN score<75THEN'中'WHEN score<90THEN'良'ELSE'优'END score_grade
,COUNT(*)over(PARTITIONBYlevel) cnt_level
FROM exam_record LEFTJOIN user_info
USING(uid)WHERE score ISNOTNULL)SELECTlevel, score_grade ,round(COUNT(uid)/cnt_level,3) ratio
FROM 
s1
GROUPBYlevel,score_grade
ORDERBYleveldesc, ratio desc;

在这里插入图片描述

3 🌈 限量查询

🚀 SQL42 注册时间最早的三个人

📖 user_info 表结构
在这里插入图片描述

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|19|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-02-0110:00:00||3|1003| 牛客3号♂      |22|0| 算法   |2020-01-0210:00:00||4|1004| 牛客4号       |25|0| 算法   |2020-01-0211:00:00||5|1005| 牛客555号     |4000|7| C++|2020-01-1110:00:00||6|1006|666666|3000|6| C++|2020-11-0110:00:00|+----+------+---------------+-------------+-------+--------+---------------------+

请从中找到注册时间最早的3个人。由示例数据结果输出如下：
+------+---------------+---------------------+| uid  | nick_name     | register_time       |+------+---------------+---------------------+|1001| 牛客1|2020-01-0110:00:00||1003| 牛客3号♂      |2020-01-0210:00:00||1004| 牛客4号       |2020-01-0211:00:00|+------+---------------+---------------------+
解释：按注册时间排序后选取前三名，输出其用户ID、昵称、注册时间。

🚀 建表语句
droptableifexists user_info,exam_record;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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-02-01 10:00:00'),(1003,'牛客3号♂',22,0,'算法','2020-01-02 10:00:00'),(1004,'牛客4号',25,0,'算法','2020-01-02 11:00:00'),(1005,'牛客555号',4000,7,'C++','2020-01-11 10:00:00'),(1006,'666666',3000,6,'C++','2020-11-01 10:00:00');

🍌🍌 答案
select uid, nick_name, register_time
from
user_info
orderby register_time
limit3;

在这里插入图片描述

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

📖 user_info 表结构
在这里插入图片描述
📖 examination_info 表结构

📖 exam_record 表结构

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+---------------+-------------+-------+--------+---------------------+| id | uid  | nick_name     | achievement |level| job    | register_time       |+----+------+---------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|19|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号       |1200|3| 算法   |2020-01-0110:00:00||3|1003| 牛客3号♂      |22|0| 算法   |2020-01-0110:00:00||4|1004| 牛客4号       |25|0| 算法   |2020-01-0111:00:00||5|1005| 牛客555号     |4000|7| 算法   |2020-01-0110:00:00||6|1006| 牛客6号       |25|0| 算法   |2020-01-0211:00:00||7|1007| 牛客7号       |25|0| 算法   |2020-01-0211:00:00||8|1008| 牛客8号       |25|0| 算法   |2020-01-0211:00:00||9|1009| 牛客9号       |25|0| 算法   |2020-01-0211:00:00||10|1010| 牛客10号      |25|0| 算法   |2020-01-0211:00:00||11|1011|666666|3000|6| C++|2020-01-0210:00:00|+----+------+---------------+-------------+-------+--------+---------------------+

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+| id | exam_id | tag    | difficulty | duration | release_time        |+----+---------+--------+------------+----------+---------------------+|1|9001| 算法   | hard       |60|2020-01-0110:00:00||2|9002| 算法   | hard       |80|2020-01-0110:00:00||3|9003|SQL| medium     |70|2020-01-0110: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-0109:01:01|2020-01-0109:21:59|80||2|1002|9003|2020-01-2010:01:01|2020-01-2010:10:01|81||3|1002|9002|2020-01-0112:11:01|2020-01-0112:31:01|83||4|1003|9002|2020-01-0119:01:01|2020-01-0119:30:01|75||5|1004|9002|2020-01-0112:01:01|2020-01-0112:11:01|60||6|1005|9002|2020-01-0112:01:01|2020-01-0112:41:01|90||7|1006|9001|2020-01-0219:01:01|2020-01-0219:32:00|20||8|1007|9002|2020-01-0219:01:01|2020-01-0219:40:01|89||9|1008|9003|2020-01-0212:01:01|2020-01-0212:20:01|99||10|1008|9001|2020-01-0212:01:01|2020-01-0212:31:01|98||11|1009|9002|2020-01-0212:01:01|2020-01-0212:31:01|82||12|1010|9002|2020-01-0212:11:01|2020-01-0212:41:01|76||13|1011|9001|2020-01-0210:01:01|2020-01-0210:31:01|89|+----+------+---------+---------------------+---------------------+-------+

找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。
排名榜很长，我们将采用分页展示，每页3条，现在需要你取出第3页（页码从1开始）的人的信息。
由示例数据结果输出如下：
+------+-------+---------------------+-----------+| uid  |level| register_time       | max_score |+------+-------+---------------------+-----------+|1010|0|2020-01-0211:00:00|76||1003|0|2020-01-0110:00:00|75||1004|0|2020-01-0111:00:00|60|+------+-------+---------------------+-----------+

🚀 建表语句
droptableifexists examination_info,user_info,exam_record;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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 11:00:00'),(1005,'牛客555号',4000,7,'算法','2020-01-01 10:00:00'),(1006,'牛客6号',25,0,'算法','2020-01-02 11:00:00'),(1007,'牛客7号',25,0,'算法','2020-01-02 11:00:00'),(1008,'牛客8号',25,0,'算法','2020-01-02 11:00:00'),(1009,'牛客9号',25,0,'算法','2020-01-02 11:00:00'),(1010,'牛客10号',25,0,'算法','2020-01-02 11:00:00'),(1011,'666666',3000,6,'C++','2020-01-02 10:00:00');INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'算法','hard',60,'2020-01-01 10:00:00'),(9002,'算法','hard',80,'2020-01-01 10:00:00'),(9003,'SQL','medium',70,'2020-01-01 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2020-01-01 09:01:01','2020-01-01 09:21:59',80),(1002,9003,'2020-01-20 10:01:01','2020-01-20 10:10:01',81),(1002,9002,'2020-01-01 12:11:01','2020-01-01 12:31:01',83),(1003,9002,'2020-01-01 19:01:01','2020-01-01 19:30:01',75),(1004,9002,'2020-01-01 12:01:01','2020-01-01 12:11:01',60),(1005,9002,'2020-01-01 12:01:01','2020-01-01 12:41:01',90),(1006,9001,'2020-01-02 19:01:01','2020-01-02 19:32:00',20),(1007,9002,'2020-01-02 19:01:01','2020-01-02 19:40:01',89),(1008,9003,'2020-01-02 12:01:01','2020-01-02 12:20:01',99),(1008,9001,'2020-01-02 12:01:01','2020-01-02 12:31:01',98),(1009,9002,'2020-01-02 12:01:01','2020-01-02 12:31:01',82),(1010,9002,'2020-01-02 12:11:01','2020-01-02 12:41:01',76),(1011,9001,'2020-01-02 10:01:01','2020-01-02 10:31:01',89);

🍌🍌 答案
select a.uid,b.level,b.register_time,max(a.score)as max_score from exam_record a
innerjoin user_info b
on a.uid=b.uid
innerjoin examination_info c
on a.exam_id=c.exam_id
GROUPby  a.uid,b.level,b.register_time
having a.uid in(select a.uid from exam_record a
innerjoin user_info b
on a.uid=b.uid
innerjoin examination_info c
on a.exam_id=c.exam_id
where b.job='算法'and c.tag='算法'and
DATE_FORMAT(a.submit_time,'%Y%m%d')=DATE_FORMAT(b.register_time,'%Y%m%d'))orderby max_score desclimit6,3;

在这里插入图片描述

4 🌈 文本转换函数

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

📖 examination_info 表结构
在这里插入图片描述

🚀 题目描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+------------------+------------+----------+---------------------+| id | exam_id | tag              | difficulty | duration | release_time        |+----+---------+------------------+------------+----------+---------------------+|1|9001| 算法             | hard       |60|2020-01-0110:00:00||2|9002| 算法             | hard       |80|2020-01-0110:00:00||3|9003|SQL| medium     |70|2020-01-0110:00:00||4|9004| 算法,medium,80||0|2020-01-0110:00:00|+----+---------+------------------+------------+----------+---------------------+

录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，
请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。
由示例数据结果输出如下：
+---------+--------+------------+----------+| exam_id | tag    | difficulty | duration |+---------+--------+------------+----------+|9004| 算法   | medium     |80|+---------+--------+------------+----------+

🚀 建表语句
droptableifexists examination_info,exam_record;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'算法','hard',60,'2020-01-01 10:00:00'),(9002,'算法','hard',80,'2020-01-01 10:00:00'),(9003,'SQL','medium',70,'2020-01-01 10:00:00'),(9004,'算法,medium,80','',0,'2020-01-01 10:00:00');

🍌🍌 答案
select exam_id
,substring_index(tag,',',1) tag
,substring_index(substring_index(tag,',',2),',',-1) difficulty
,substring_index(tag,',',-1) duration
from examination_info
where difficulty='';

在这里插入图片描述

🚀 SQL45 对过长的昵称截取处理

📖 examination_info 表结构
在这里插入图片描述

🚀 题目描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值,level等级, job职业方向, register_time注册时间）：
+----+------+-------------------------+-------------+-------+--------+---------------------+| id | uid  | nick_name               | achievement |level| job    | register_time       |+----+------+-------------------------+-------------+-------+--------+---------------------+|1|1001| 牛客1|19|0| 算法   |2020-01-0110:00:00||2|1002| 牛客2号                 |1200|3| 算法   |2020-01-0110:00:00||3|1003| 牛客3号♂                |22|0| 算法   |2020-01-0110:00:00||4|1004| 牛客4号                 |25|0| 算法   |2020-01-0111:00:00||5|1005| 牛客5678901234号        |4000|7| 算法   |2020-01-0110:00:00||6|1006| 牛客67890123456789号    |25|0| 算法   |2020-01-0211:00:00|+----+------+-------------------------+-------------+-------+--------+---------------------+

有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，
请输出字符数大于10的用户信息，对于字符数大于13的用户输出前10个字符然后加上三个点号：『...』。
由示例数据结果输出如下：
+------+---------------------+| uid  | nick_name           |+------+---------------------+|1005| 牛客5678901234号    ||1006| 牛客67890123...|+------+---------------------+
解释：字符数大于10的用户有1005和1006，长度分别为13、17；因此需要对1006的昵称截断输出。

🚀 建表语句
droptableifexists user_info;CREATETABLE user_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
    achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
    job varchar(32)COMMENT'职业方向',
    register_time datetimeCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO 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 11:00:00'),(1005,'牛客5678901234号',4000,7,'算法','2020-01-01 10:00:00'),(1006,'牛客67890123456789号',25,0,'算法','2020-01-02 11:00:00');

🍌🍌 答案
SELECT uid,IF(
   CHAR_LENGTH(nick_name)>13, CONCAT(LEFT(nick_name,10),"..."), nick_name
) nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name)>10;

在这里插入图片描述

🚀 SQL45 对过长的昵称截取处理

📖 examination_info 表结构
在这里插入图片描述
📖 exam_record 表结构

🚀 题目描述
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+| id | exam_id | tag    | difficulty | duration | release_time        |+----+---------+--------+------------+----------+---------------------+|1|9001| 算法   | hard       |60|2020-01-0110:00:00||2|9002| C++| hard       |80|2020-01-0110:00:00||3|9003| c++| hard       |80|2020-01-0110:00:00||4|9004|sql| medium     |70|2020-01-0110:00:00||5|9005| C++| hard       |80|2020-01-0110:00:00||6|9006| C++| hard       |80|2020-01-0110:00:00||7|9007| C++| hard       |80|2020-01-0110:00:00||8|9008|SQL| medium     |70|2020-01-0110:00:00||9|9009|SQL| medium     |70|2020-01-0110:00:00||10|9010|SQL| medium     |70|2020-01-0110: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-0109:01:01|2020-01-0109:21:59|80||2|1002|9003|2020-01-2010:01:01|2020-01-2010:10:01|81||3|1002|9002|2020-01-0112:11:01|2020-01-0112:31:01|83||4|1003|9002|2020-01-0119:01:01|2020-01-0119:30:01|75||5|1004|9002|2020-01-0112:01:01|2020-01-0112:11:01|60||6|1005|9002|2020-01-0112:01:01|2020-01-0112:41:01|90||7|1006|9001|2020-01-0219:01:01|2020-01-0219:32:00|20||8|1007|9003|2020-01-0219:01:01|2020-01-0219:40:01|89||9|1008|9004|2020-01-0212:01:01|2020-01-0212:20:01|99||10|1008|9001|2020-01-0212:01:01|2020-01-0212:31:01|98||11|1009|9002|2020-01-0212:01:01|2020-01-0212:43:01|81||12|1010|9002|2020-01-0212:11:01|NULL|NULL||13|1011|9001|2020-01-0210:01:01|2020-01-0210:31:01|89|+----+------+---------+---------------------+---------------------+-------+

试卷的类别tag可能出现大小写混乱的情况，请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。
如果转换后tag并没有发生变化，不输出该条结果。
由示例数据结果输出如下：
+------+------------+| tag  | answer_cnt |+------+------------+| c++|6|+------+------------+

🚀 建表语句
droptableifexists examination_info,exam_record;CREATETABLE examination_info (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
    tag varchar(32)COMMENT'类别标签',
    difficulty varchar(8)COMMENT'难度',
    duration intNOTNULLCOMMENT'时长',
    release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_bin;CREATETABLE exam_record (
    id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
    uid intNOTNULLCOMMENT'用户ID',
    exam_id intNOTNULLCOMMENT'试卷ID',
    start_time datetimeNOTNULLCOMMENT'开始时间',
    submit_time datetimeCOMMENT'提交时间',
    score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'算法','hard',60,'2020-01-01 10:00:00'),(9002,'C++','hard',80,'2020-01-01 10:00:00'),(9003,'c++','hard',80,'2020-01-01 10:00:00'),(9004,'sql','medium',70,'2020-01-01 10:00:00'),(9005,'C++','hard',80,'2020-01-01 10:00:00'),(9006,'C++','hard',80,'2020-01-01 10:00:00'),(9007,'C++','hard',80,'2020-01-01 10:00:00'),(9008,'SQL','medium',70,'2020-01-01 10:00:00'),(9009,'SQL','medium',70,'2020-01-01 10:00:00'),(9010,'SQL','medium',70,'2020-01-01 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2020-01-01 09:01:01','2020-01-01 09:21:59',80),(1002,9003,'2020-01-20 10:01:01','2020-01-20 10:10:01',81),(1002,9002,'2020-01-01 12:11:01','2020-01-01 12:31:01',83),(1003,9002,'2020-01-01 19:01:01','2020-01-01 19:30:01',75),(1004,9002,'2020-01-01 12:01:01','2020-01-01 12:11:01',60),(1005,9002,'2020-01-01 12:01:01','2020-01-01 12:41:01',90),(1006,9001,'2020-01-02 19:01:01','2020-01-02 19:32:00',20),(1007,9003,'2020-01-02 19:01:01','2020-01-02 19:40:01',89),(1008,9004,'2020-01-02 12:01:01','2020-01-02 12:20:01',99),(1008,9001,'2020-01-02 12:01:01','2020-01-02 12:31:01',98),(1009,9002,'2020-01-02 12:01:01','2020-01-02 12:43:01',81),(1010,9002,'2020-01-02 12:11:01',null,null),(1011,9001,'2020-01-02 10:01:01','2020-01-02 10:31:01',89);

🍌🍌 答案
WITH t_tag_count as(SELECT tag,COUNT(uid)as answer_cnt
    FROM exam_record
    LEFTJOIN examination_info USING(exam_id)GROUPBY tag
)SELECT a.tag, b.answer_cnt
FROM t_tag_count as a
JOIN t_tag_count as b
ON UPPER(a.tag)= b.tag and a.tag != b.tag and a.answer_cnt <3;

在这里插入图片描述

标签： sql dba 数据库

本文转载自: https://blog.csdn.net/weixin_41645135/article/details/125128318
版权归原作者 IT邦德 所有，如有侵权，请联系我们删除。

【牛客刷题-SQL进阶挑战】NO6.其他常用操作

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

1 🌈 空值处理

🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率

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

2 🌈 高级条件语句

🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率

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

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

🚀 SQL41 各用户等级的不同得分表现占比

3 🌈 限量查询

🚀 SQL42 注册时间最早的三个人

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

4 🌈 文本转换函数

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

🚀 SQL45 对过长的昵称截取处理

🚀 SQL45 对过长的昵称截取处理

发表评论

“【牛客刷题-SQL进阶挑战】NO6.其他常用操作”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航