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

文章目录

• 前言
• • • 1 🌈 空值处理
    • • 🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率
      • 🚀 SQL37 0级用户高难度试卷的平均用时和平均得分
    • 2 🌈 高级条件语句
    • • 🚀 SQL36 统计有未完成状态的试卷的未完成数和未完成率
      • 🚀 SQL39 筛选昵称规则和试卷规则的作答记录
      • 🚀 SQL40 根据指定记录是否存在输出不同情况
      • 🚀 SQL41 各用户等级的不同得分表现占比
    • 3 🌈 限量查询
    • • 🚀 SQL42 注册时间最早的三个人
      • 🚀 SQL39 筛选昵称规则和试卷规则的作答记录
    • 4 🌈 文本转换函数
    • • 🚀 SQL39 筛选昵称规则和试卷规则的作答记录
      • 🚀 SQL45 对过长的昵称截取处理
      • 🚀 SQL45 对过长的昵称截取处理

前言

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-02 09:01:01 | 2020-01-02 09:21:01 |    80 ||  2 | 1001 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:30:01 |    81 ||  3 | 1001 |    9001 | 2021-09-02 12: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位小数）🚀 建表语句drop table if exists exam_record;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 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(case when score is null then 1 else 0 end) incomplete_cnt,round((count(*)-count(submit_time))/count(*),3) incomplete_rate from exam_recordgroup by exam_idHAVING (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-01 10:00:00 ||  2 | 1002 | 牛客2号    | 2100 |     6 | 算法   | 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 | 2020-01-01 10:00:00 ||  2 |    9002 | SQL    | easy|60 | 2020-01-01 10:00:00 ||  3 |    9004 | 算法   | medium     |80 | 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 ||  3 | 1001 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 ||  4 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 |    20 ||  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 ||  7 | 1002 |    9002 | 2021-05-05 18:01:01 | 2021-05-05 18: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分（取整）🚀 建表语句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);🍌🍌 答案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_tookFROM user_info uiLEFT JOIN exam_record er USING(uid)LEFT JOIN examination_info ei USING(exam_id)WHERE level = 0AND difficulty = 'hard'GROUP BY 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-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 | 牛客5号| 3000 |     7 | C++    | 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 ||  3 | 1001 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 ||  4 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 |    20 ||  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 ||  7 | 1002 |    9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 |    90 ||  8 | 1003 |    9003 | 2021-02-06 12:01:01 | NULL  |  NULL ||  9 | 1003 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    89 || 10 | 1004 |    9002 | 2021-08-06 12:01:01 | NULL  |  NULL || 11 | 1002 |    9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 |    81 || 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 || 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-02-01 11:31:01 |    84 || 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 |+----+------+---------+---------------------+---------------------+-------+题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：+----+------+-------------+---------------------+-------+| 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月的用户信息。由示例数据结果输出如下：+------+------------+-------------+| uid  | nick_name  | achievement |+------+------------+-------------+| 1002 | 牛客2号    | 1200 |+------+------------+-------------+解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；1002最近一次试卷区活跃为2021年9月，最近一次题目区活跃为2021年9月；1004最近一次试卷区活跃为2021年8月，题目区未活跃。因此最终满足条件的只有1002。🚀 建表语句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);🍌🍌 答案select uid, nick_name, achievementfrom(    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) Xwhere 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-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 ||  3 | 1001 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 ||  4 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 |    20 ||  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 ||  7 | 1002 |    9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 |    90 ||  8 | 1003 |    9003 | 2021-02-06 12:01:01 | NULL  |  NULL ||  9 | 1003 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 |    89 || 10 | 1004 |    9002 | 2021-08-06 12:01:01 | NULL  |  NULL || 11 | 1002 |    9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 |    81 || 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 || 14 | 1005 |    9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 |    84 || 15 | 1006 |    9001 | 2021-09-01 11:01:01 | 2021-09-01 11:31:01 |    84 || 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 |+----+------+---------+---------------------+---------------------+-------+找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母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；🚀 建表语句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);🍌🍌 答案select u_i.uid as uid, e_r.exam_id as exam_id,round(avg(score), 0) as avg_scorefrom exam_record e_r join examination_info e_ion e_r.exam_id = e_i.exam_idjoin user_info u_ion e_r.uid = u_i.uidwhere 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_idorder by 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-01 10:00:00 ||  2 | 1002 | 牛客2号| 1200 |     3 | 算法   | 2020-01-01 10:00:00 ||  3 | 1003 | 牛客3号♂      |   22 |     0 | 算法   | 2020-01-01 10:00:00 ||  4 | 1004 | 牛客4号|   25 |     0 | 算法   | 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 |+----+------+---------------+-------------+-------+--------+---------------------+试卷作答记录表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 ||  3 | 1001 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    87 ||  4 | 1001 |    9002 | 2021-09-01 12:01:01 | NULL  |  NULL ||  5 | 1001 |    9003 | 2021-09-02 12:01:01 | NULL  |  NULL ||  6 | 1001 |    9004 | 2021-09-03 12:01:01 | NULL  |  NULL ||  7 | 1002 |    9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 |    99 ||  8 | 1002 |    9003 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 |    82 ||  9 | 1002 |    9003 | 2020-02-02 12:11:01 | NULL  |  NULL || 10 | 1002 |    9002 | 2021-05-05 18:01:01 | NULL  |  NULL || 11 | 1002 |    9001 | 2021-09-06 12:01:01 | NULL  |  NULL || 12 | 1003 |    9003 | 2021-02-06 12:01:01 | NULL  |  NULL || 13 | 1003 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10: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的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。🚀 建表语句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);🍌🍌 答案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(partition by level) as max_incomplete_cnt    from exam_record a    right join user_info b on a.uid = b.uid    group by b.uid,`level`)select uid,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_ratefrom t_tag_cnt where `level`=0 and max_incomplete_cnt>2unionselect uid,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_ratefrom t_tag_cnt where not EXists( select uid from t_tag_cnt where `level`=0 and max_incomplete_cnt>2) and complete_cnt != 0order by 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-01 10:00:00 ||  2 | 1002 | 牛客2号| 1200 |     3 | 算法   | 2020-01-01 10:00:00 ||  3 | 1003 | 牛客3号♂      |   22 |     0 | 算法   | 2020-01-01 10:00:00 ||  4 | 1004 | 牛客4号|   25 |     0 | 算法   | 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 |+----+------+---------------+-------------+-------+--------+---------------------+试卷作答记录表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 ||  3 | 1001 |    9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 |    75 ||  4 | 1001 |    9002 | 2021-09-01 12:01:01 | 2021-09-01 12:11:01 |    60 ||  5 | 1001 |    9003 | 2021-09-02 12:01:01 | 2021-09-02 12:41:01 |    90 ||  6 | 1001 |    9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 |    20 ||  7 | 1001 |    9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 ||  8 | 1001 |    9004 | 2021-09-03 12:01:01 | NULL  |  NULL ||  9 | 1002 |    9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 |    99 || 10 | 1002 |    9003 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 |    82 || 11 | 1002 |    9003 | 2020-02-02 12:11:01 | 2020-02-02 12: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 |+-------+-------------+-------+🚀 建表语句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', 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,CASE WHEN score<60 THEN '差'WHEN score<75 THEN '中'WHEN score<90 THEN '良'ELSE '优' END score_grade, COUNT(*) over(PARTITION BY level) cnt_levelFROM exam_record LEFT JOIN user_infoUSING(uid)WHERE score IS NOT NULL)SELECT level , score_grade , round(COUNT(uid)/cnt_level,3) ratioFROM s1GROUP BY level,score_gradeORDER BY level desc , 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-01 10:00:00 ||  2 | 1002 | 牛客2号| 1200 |     3 | 算法   | 2020-02-01 10:00:00 ||  3 | 1003 | 牛客3号♂      |   22 |     0 | 算法   | 2020-01-02 10:00:00 ||  4 | 1004 | 牛客4号|   25 |     0 | 算法   | 2020-01-02 11:00:00 ||  5 | 1005 | 牛客555号     | 4000 |     7 | C++    | 2020-01-11 10:00:00 ||  6 | 1006 | 666666 | 3000 |     6 | C++    | 2020-11-01 10:00:00 |+----+------+---------------+-------------+-------+--------+---------------------+请从中找到注册时间最早的3个人。由示例数据结果输出如下：+------+---------------+---------------------+| uid  | nick_name     | register_time|+------+---------------+---------------------+| 1001 | 牛客1  | 2020-01-01 10:00:00 || 1003 | 牛客3号♂      | 2020-01-02 10:00:00 || 1004 | 牛客4号| 2020-01-02 11:00:00 |+------+---------------+---------------------+解释：按注册时间排序后选取前三名，输出其用户ID、昵称、注册时间。🚀 建表语句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;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-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_timefromuser_infoorder by register_timelimit 3;

🚀 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-01 10:00:00 ||  2 | 1002 | 牛客2号| 1200 |     3 | 算法   | 2020-01-01 10:00:00 ||  3 | 1003 | 牛客3号♂      |   22 |     0 | 算法   | 2020-01-01 10:00:00 ||  4 | 1004 | 牛客4号|   25 |     0 | 算法   | 2020-01-01 11:00:00 ||  5 | 1005 | 牛客555号     | 4000 |     7 | 算法   | 2020-01-01 10:00:00 ||  6 | 1006 | 牛客6号|   25 |     0 | 算法   | 2020-01-02 11:00:00 ||  7 | 1007 | 牛客7号|   25 |     0 | 算法   | 2020-01-02 11:00:00 ||  8 | 1008 | 牛客8号|   25 |     0 | 算法   | 2020-01-02 11:00:00 ||  9 | 1009 | 牛客9号|   25 |     0 | 算法   | 2020-01-02 11:00:00 || 10 | 1010 | 牛客10号      |   25 |     0 | 算法   | 2020-01-02 11:00:00 || 11 | 1011 | 666666 | 3000 |     6 | C++    | 2020-01-02 10: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-01 10:00:00 ||  2 |    9002 | 算法   | 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-01 09:01:01 | 2020-01-01 09:21:59 |    80 ||  2 | 1002 |    9003 | 2020-01-20 10:01:01 | 2020-01-20 10:10:01 |    81 ||  3 | 1002 |    9002 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 |    83 ||  4 | 1003 |    9002 | 2020-01-01 19:01:01 | 2020-01-01 19:30:01 |    75 ||  5 | 1004 |    9002 | 2020-01-01 12:01:01 | 2020-01-01 12:11:01 |    60 ||  6 | 1005 |    9002 | 2020-01-01 12:01:01 | 2020-01-01 12:41:01 |    90 ||  7 | 1006 |    9001 | 2020-01-02 19:01:01 | 2020-01-02 19:32:00 |    20 ||  8 | 1007 |    9002 | 2020-01-02 19:01:01 | 2020-01-02 19:40:01 |    89 ||  9 | 1008 |    9003 | 2020-01-02 12:01:01 | 2020-01-02 12:20:01 |    99 || 10 | 1008 |    9001 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 |    98 || 11 | 1009 |    9002 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 |    82 || 12 | 1010 |    9002 | 2020-01-02 12:11:01 | 2020-01-02 12:41:01 |    76 || 13 | 1011 |    9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 |    89 |+----+------+---------+---------------------+---------------------+-------+找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。排名榜很长，我们将采用分页展示，每页3条，现在需要你取出第3页（页码从1开始）的人的信息。由示例数据结果输出如下：+------+-------+---------------------+-----------+| uid  | level | register_time| max_score |+------+-------+---------------------+-----------+| 1010 |     0 | 2020-01-02 11:00:00 | 76 || 1003 |     0 | 2020-01-01 10:00:00 | 75 || 1004 |     0 | 2020-01-01 11:00:00 | 60 |+------+-------+---------------------+-----------+🚀 建表语句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', 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');INSERT INTO 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');INSERT INTO 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 ainner join user_info bon a.uid=b.uidinner join examination_info con a.exam_id=c.exam_idGROUP by  a.uid,b.level,b.register_timehaving a.uid in(select a.uid from exam_record ainner join user_info bon a.uid=b.uidinner join examination_info con a.exam_id=c.exam_idwhere b.job='算法' and c.tag='算法' andDATE_FORMAT(a.submit_time,'%Y%m%d')=DATE_FORMAT(b.register_time,'%Y%m%d'))order by max_score desclimit 6,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-01 10:00:00 ||  2 |    9002 | 算法      | hard|80 | 2020-01-01 10:00:00 ||  3 |    9003 | SQL| medium     |70 | 2020-01-01 10:00:00 ||  4 |    9004 | 算法,medium,80   |     | 0 | 2020-01-01 10:00:00 |+----+---------+------------------+------------+----------+---------------------+录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。由示例数据结果输出如下：+---------+--------+------------+----------+| exam_id | tag    | difficulty | duration |+---------+--------+------------+----------+|    9004 | 算法   | medium     | 80|+---------+--------+------------+----------+🚀 建表语句drop table if exists examination_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;INSERT INTO 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) durationfrom examination_infowhere 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-01 10:00:00 ||  2 | 1002 | 牛客2号   | 1200 |     3 | 算法   | 2020-01-01 10:00:00 ||  3 | 1003 | 牛客3号♂  |   22 |     0 | 算法   | 2020-01-01 10:00:00 ||  4 | 1004 | 牛客4号   |   25 |     0 | 算法   | 2020-01-01 11:00:00 ||  5 | 1005 | 牛客5678901234号 | 4000 |     7 | 算法   | 2020-01-01 10:00:00 ||  6 | 1006 | 牛客67890123456789号    |   25 |     0 | 算法   | 2020-01-02 11:00:00 |+----+------+-------------------------+-------------+-------+--------+---------------------+有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，请输出字符数大于10的用户信息，对于字符数大于13的用户输出前10个字符然后加上三个点号：『...』。由示例数据结果输出如下：+------+---------------------+| uid  | nick_name    |+------+---------------------+| 1005 | 牛客5678901234号    || 1006 | 牛客67890123...     |+------+---------------------+解释：字符数大于10的用户有1005和1006，长度分别为13、17；因此需要对1006的昵称截断输出。🚀 建表语句drop table if exists user_info;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;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 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_nameFROM user_infoWHERE CHAR_LENGTH(nick_name) > 10;