0
0

【牛客刷题-SQL进阶挑战】NO4.多表查询

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

文章目录

前言

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

在这里插入图片描述

1 🌈 嵌套子查询

🚀 SQL20 月均完成试卷数不小于3的用户爱作答的类别

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

🚀 题目描述
现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分），示例数据如下：
+----+------+---------+---------------------+---------------------+-------+| id | uid  | exam_id | start_time          | submit_time         | score |+----+------+---------+---------------------+---------------------+-------+|1|1001|9001|2021-07-0209:01:01|NULL|NULL||2|1002|9003|2021-09-0112:01:01|2021-09-0112:21:01|60||3|1002|9002|2021-09-0212:01:01|2021-09-0212:31:01|70||4|1002|9001|2021-09-0519:01:01|2021-09-0519:40:01|81||5|1002|9002|2021-07-0612:01:01|NULL|NULL||6|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||7|1003|9003|2021-09-0812:01:01|2021-09-0812:11:01|40||8|1003|9001|2021-09-0813:01:01|NULL|NULL||9|1003|9002|2021-09-0814:01:01|NULL|NULL||10|1003|9003|2021-09-0815:01:01|NULL|NULL||11|1005|9001|2021-09-0112:01:01|2021-09-0112:31:01|88||12|1005|9002|2021-09-0112:01:01|2021-09-0112:31:01|88||13|1005|9002|2021-09-0212:11:01|2021-09-0212:31:01|89|+----+------+---------+---------------------+---------------------+-------+

试卷信息表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| C++| easy       |60|2020-02-0110:00:00||3|9003| 算法   | medium     |80|2020-08-0210:00:00|+----+---------+--------+------------+----------+---------------------+

请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：

解释：用户1002和1005在2021年09月的完成试卷数目均为3，其他用户均小于3；
然后用户1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。

🚀 建表语句
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;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,'SQL','hard',60,'2020-01-01 10:00:00'),(9002,'C++','easy',60,'2020-02-01 10:00:00'),(9003,'算法','medium',80,'2020-08-02 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-07-02 09:01:01',null,null),(1002,9003,'2021-09-01 12:01:01','2021-09-01 12:21:01',60),(1002,9002,'2021-09-02 12:01:01','2021-09-02 12:31:01',70),(1002,9001,'2021-09-05 19:01:01','2021-09-05 19:40:01',81),(1002,9002,'2021-07-06 12:01:01',null,null),(1003,9003,'2021-09-07 10:01:01','2021-09-07 10:31:01',86),(1003,9003,'2021-09-08 12:01:01','2021-09-08 12:11:01',40),(1003,9001,'2021-09-08 13:01:01',null,null),(1003,9002,'2021-09-08 14:01:01',null,null),(1003,9003,'2021-09-08 15:01:01',null,null),(1005,9001,'2021-09-01 12:01:01','2021-09-01 12:31:01',88),(1005,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',88),(1005,9002,'2021-09-02 12:11:01','2021-09-02 12:31:01',89);

🍌🍌 答案
select tag,count(start_time)as tag_cnt
from exam_record er innerjoin examination_info ei
on er.exam_id = ei.exam_id
where uid in(select uid
from exam_record er 
groupby uid,month(start_time)havingcount(submit_time)>=3)groupby tag
orderby tag_cnt desc;

在这里插入图片描述

🚀 SQL21 试卷发布当天作答人数和平均分

📖 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号    |3100|7| 算法   |2020-01-0110:00:00||2|1002| 牛客2号    |2100|6| 算法   |2020-01-0110:00:00||3|1003| 牛客3号    |1500|5| 算法   |2020-01-0110:00:00||4|1004| 牛客4号    |1100|4| 算法   |2020-01-0110:00:00||5|1005| 牛客5号    |1600|6| C++|2020-01-0110:00:00||6|1006| 牛客6号    |3000|6| C++|2020-01-0110:00:00|+----+------+------------+-------------+-------+--------+---------------------+
释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间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|2021-09-0106:00:00||2|9002| C++| easy       |60|2020-02-0110:00:00||3|9003| 算法   | medium     |80|2020-08-0210: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|2021-09-0109:01:01|2021-09-0109:41:01|70||2|1002|9003|2021-09-0112:01:01|2021-09-0112:21:01|60||3|1002|9002|2021-09-0212:01:01|2021-09-0212:31:01|70||4|1002|9001|2021-09-0119:01:01|2021-09-0119:40:01|80||5|1002|9003|2021-08-0112:01:01|2021-08-0112:21:01|60||6|1002|9002|2021-08-0212:01:01|2021-08-0212:31:01|70||7|1002|9001|2021-09-0119:01:01|2021-09-0119:40:01|85||8|1002|9002|2021-07-0612:01:01|NULL|NULL||9|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||10|1003|9003|2021-09-0812:01:01|2021-09-0812:11:01|40||11|1003|9001|2021-09-0113:01:01|2021-09-0113:41:01|70||12|1003|9002|2021-09-0814:01:01|NULL|NULL||13|1003|9003|2021-09-0815:01:01|NULL|NULL||14|1005|9001|2021-09-0112:01:01|2021-09-0112:31:01|90||15|1005|9002|2021-09-0112:01:01|2021-09-0112:31:01|88||16|1005|9002|2021-09-0212:11:01|2021-09-0212:31:01|89|+----+------+---------+---------------------+---------------------+-------+

请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：
+---------+----+-----------+| exam_id | uv | avg_score |+---------+----+-----------+|9001|3|81.3|+---------+----+-----------+

解释：只有一张SQL类别的试卷，试卷ID为9001，发布当天（2021-09-01）有1001、1002、1003、1005作答过，
但是1003是5级用户，其他3位为5级以上，他们三的得分有[70,80,85,90]，平均分为81.3（保留1位小数）。

🚀 建表语句
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号',3100,7,'算法','2020-01-01 10:00:00'),(1002,'牛客2号',2100,6,'算法','2020-01-01 10:00:00'),(1003,'牛客3号',1500,5,'算法','2020-01-01 10:00:00'),(1004,'牛客4号',1100,4,'算法','2020-01-01 10:00:00'),(1005,'牛客5号',1600,6,'C++','2020-01-01 10:00:00'),(1006,'牛客6号',3000,6,'C++','2020-01-01 10:00:00');INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'SQL','hard',60,'2021-09-01 06:00:00'),(9002,'C++','easy',60,'2020-02-01 10:00:00'),(9003,'算法','medium',80,'2020-08-02 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:41:01',70),(1002,9003,'2021-09-01 12:01:01','2021-09-01 12:21:01',60),(1002,9002,'2021-09-02 12:01:01','2021-09-02 12:31:01',70),(1002,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',80),(1002,9003,'2021-08-01 12:01:01','2021-08-01 12:21:01',60),(1002,9002,'2021-08-02 12:01:01','2021-08-02 12:31:01',70),(1002,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',85),(1002,9002,'2021-07-06 12:01:01',null,null),(1003,9003,'2021-09-07 10:01:01','2021-09-07 10:31:01',86),(1003,9003,'2021-09-08 12:01:01','2021-09-08 12:11:01',40),(1003,9001,'2021-09-01 13:01:01','2021-09-01 13:41:01',70),(1003,9002,'2021-09-08 14:01:01',null,null),(1003,9003,'2021-09-08 15:01:01',null,null),(1005,9001,'2021-09-01 12:01:01','2021-09-01 12:31:01',90),(1005,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',88),(1005,9002,'2021-09-02 12:11:01','2021-09-02 12:31:01',89);

🍌🍌 答案
SELECT
    exam_id,count(DISTINCT uid )AS uv,ROUND(avg( score ),1)AS avg_score
FROM exam_record
WHERE(exam_id,DATE(start_time))IN(SELECT exam_id,DATE(release_time)FROM examination_info WHERE tag ="SQL")AND uid IN(SELECT uid FROM user_info WHERE`level`>5)GROUPBY exam_id
ORDERBY uv DESC, avg_score ASC;
备注：
结果按人数uv降序，相同人数的按平均分升序

在这里插入图片描述

🚀 SQL22 作答试卷得分大于过80的人的用户等级分布

📖 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号    |3100|7| 算法   |2020-01-0110:00:00||2|1002| 牛客2号    |2100|6| 算法   |2020-01-0110:00:00||3|1003| 牛客3号    |1500|5| 算法   |2020-01-0110:00:00||4|1004| 牛客4号    |1100|4| 算法   |2020-01-0110:00:00||5|1005| 牛客5号    |1600|6| C++|2020-01-0110:00:00||6|1006| 牛客6号    |3000|6| C++|2020-01-0110:00:00|+----+------+------------+-------------+-------+--------+---------------------+
释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间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|2021-09-0106:00:00||2|9002| C++| easy       |60|2020-02-0110:00:00||3|9003| 算法   | medium     |80|2020-08-0210: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|2021-09-0109:01:01|2021-09-0109:41:01|70||2|1002|9003|2021-09-0112:01:01|2021-09-0112:21:01|60||3|1002|9002|2021-09-0212:01:01|2021-09-0212:31:01|70||4|1002|9001|2021-09-0119:01:01|2021-09-0119:40:01|80||5|1002|9003|2021-08-0112:01:01|2021-08-0112:21:01|60||6|1002|9002|2021-08-0212:01:01|2021-08-0212:31:01|70||7|1002|9001|2021-09-0119:01:01|2021-09-0119:40:01|85||8|1002|9002|2021-07-0612:01:01|NULL|NULL||9|1003|9003|2021-09-0710:01:01|2021-09-0710:31:01|86||10|1003|9003|2021-09-0812:01:01|2021-09-0812:11:01|40||11|1003|9001|2021-09-0113:01:01|2021-09-0113:41:01|70||12|1003|9002|2021-09-0814:01:01|NULL|NULL||13|1003|9003|2021-09-0815:01:01|NULL|NULL||14|1005|9001|2021-09-0112:01:01|2021-09-0112:31:01|90||15|1005|9002|2021-09-0112:01:01|2021-09-0112:31:01|88||16|1005|9002|2021-09-0212:11:01|2021-09-0212:31:01|89|+----+------+---------+---------------------+---------------------+-------+

统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）。
示例数据结果输出如下：

解释：9001为SQL类试卷，作答该试卷大于80分的人有1002、1003、1005共3人，6级两人，5级一人。

🚀 建表语句
droptableifexists examination_info;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;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;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 user_info(uid,`nick_name`,achievement,level,job,register_time)VALUES(1001,'牛客1号',3100,7,'算法','2020-01-01 10:00:00'),(1002,'牛客2号',2100,6,'算法','2020-01-01 10:00:00'),(1003,'牛客3号',1500,5,'算法','2020-01-01 10:00:00'),(1004,'牛客4号',1100,4,'算法','2020-01-01 10:00:00'),(1005,'牛客5号',1600,6,'C++','2020-01-01 10:00:00'),(1006,'牛客6号',3000,6,'C++','2020-01-01 10:00:00');INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'SQL','hard',60,'2021-09-01 06:00:00'),(9002,'C++','easy',60,'2021-09-01 06:00:00'),(9003,'算法','medium',80,'2021-09-01 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:41:01',79),(1002,9003,'2021-09-01 12:01:01','2021-09-01 12:21:01',60),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',70),(1002,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',80),(1002,9003,'2021-08-01 12:01:01','2021-08-01 12:21:01',60),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',70),(1002,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',85),(1002,9002,'2021-09-01 12:01:01',null,null),(1003,9003,'2021-09-07 10:01:01','2021-09-07 10:31:01',86),(1003,9003,'2021-09-08 12:01:01','2021-09-08 12:11:01',40),(1003,9001,'2021-09-01 13:01:01','2021-09-01 13:41:01',81),(1003,9002,'2021-09-01 14:01:01',null,null),(1003,9003,'2021-09-08 15:01:01',null,null),(1005,9001,'2021-09-01 12:01:01','2021-09-01 12:31:01',90),(1005,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',88),(1006,9002,'2021-09-01 12:11:01','2021-09-01 12:31:01',89);

🍌🍌 答案
SELECTlevel,COUNT(DISTINCT uid)AS level_cnt
FROM exam_record er JOIN user_info ui USING(uid)JOIN examination_info ei USING(exam_id)WHERE ei.tag='SQL'AND er.score >80GROUPBYlevelORDERBYCOUNT(DISTINCT uid)DESC,levelDESC;

在这里插入图片描述

2 🌈 合并查询

🚀 SQL23 每个题目和每份试卷被作答的人数和次数

📖 exam_record表结构
在这里插入图片描述
📖 practice_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|2021-09-0109:01:01|2021-09-0109:41:01|81||2|1002|9002|2021-09-0112:01:01|2021-09-0112:31:01|70||3|1002|9001|2021-09-0119:01:01|2021-09-0119:40:01|80||4|1002|9002|2021-09-0112:01:01|2021-09-0112:31:01|70||5|1004|9001|2021-09-0119:01:01|2021-09-0119:40:01|85||6|1002|9002|2021-09-0112: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|8001|2021-08-0219:38:01|70||6|1003|8001|2021-08-0219:48:01|90||7|1003|8002|2021-08-0119:38:01|80|+----+------+-------------+---------------------+-------+

请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：
+------+----+----+| tid  | uv | pv |+------+----+----+|9001|3|3||9002|1|3||8001|3|5||8002|2|2|+------+----+----+
解释：“试卷”有3人共练习3次试卷9001，1人作答3次9002；“刷题”有3人刷5次8001，有2人刷2次8002

🚀 建表语句
droptableifexists practice_record;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;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 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,8001,'2021-08-02 19:38:01',70),(1003,8001,'2021-08-02 19:48:01',90),(1003,8002,'2021-08-01 19:38:01',80);INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:41:01',81),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',70),(1002,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',80),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',70),(1004,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',85),(1002,9002,'2021-09-01 12:01:01',null,null);

🍌🍌 答案
SELECT exam_id as tid,count(distinct uid)as uv,count(uid)as pv
from exam_record
groupby exam_id
unionALLSELECT question_id as tid,count(distinct uid)as uv,count(uid)as pv
from practice_record
groupby question_id
orderbyLEFT(tid,1)DESC,uv DESC,pv DESC;

在这里插入图片描述

🚀 SQL24 分别满足两个活动的人

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

🚀 题目描述
为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。
假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、
至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。

现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。
请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，
按用户ID排序输出。

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
+----+---------+--------+------------+----------+---------------------+| id | exam_id | tag    | difficulty | duration | release_time        |+----+---------+--------+------------+----------+---------------------+|1|9001|SQL| hard       |60|2021-09-0106:00:00||2|9002| C++| hard       |60|2021-09-0106:00:00||3|9003| 算法   | medium     |80|2021-09-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|2021-09-0109:01:01|2021-09-0109:31:00|81||2|1002|9002|2021-09-0112:01:01|2021-09-0112:31:01|70||3|1003|9001|2021-09-0119:01:01|2021-09-0119:40:01|86||4|1003|9002|2021-09-0112:01:01|2021-09-0112:31:51|89||5|1004|9001|2021-09-0119:01:01|2021-09-0119:30:01|85|+----+------+---------+---------------------+---------------------+-------+

示例数据输出结果：
+------+-----------+| uid  | activity  |+------+-----------+|1001| activity2 ||1003| activity1 ||1004| activity1 ||1004| activity2 |+------+-----------+

解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；
1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；
用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2。

🚀 建表语句
droptableifexists examination_info;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;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 examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'SQL','hard',60,'2021-09-01 06:00:00'),(9002,'C++','hard',60,'2021-09-01 06:00:00'),(9003,'算法','medium',80,'2021-09-01 10:00:00');INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:31:00',81),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',70),(1003,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',86),(1003,9002,'2021-09-01 12:01:01','2021-09-01 12:31:51',89),(1004,9001,'2021-09-01 19:01:01','2021-09-01 19:30:01',85);

🍌🍌 答案
(select r.uid,'activity1'as activity
from exam_record r
whereyear(submit_time)=2021groupby r.uid
havingmin(score)>=85)unionall(select r.uid,'activity2'as activity 
from exam_record r
leftjoin examination_info i on r.exam_id = i.exam_id
whereyear(submit_time)=2021and i.difficulty ='hard'and score >=80and 
            timestampdiff(second,r.start_time,r.submit_time)<= i.duration*30groupby r.uid)orderby uid;

在这里插入图片描述

3 🌈 连接查询

🚀 SQL25 满足条件的用户的试卷完成数和题目练习数

📖 user_info表结构
在这里插入图片描述
📖 examination_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号    |3100|7| 算法   |2020-01-0110:00:00||2|1002| 牛客2号    |2300|7| 算法   |2020-01-0110:00:00||3|1003| 牛客3号    |2500|7| 算法   |2020-01-0110:00:00||4|1004| 牛客4号    |1200|5| 算法   |2020-01-0110:00:00||5|1005| 牛客5号    |1600|6| C++|2020-01-0110:00:00||6|1006| 牛客6号    |2000|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|SQL| hard       |60|2021-09-0106:00:00||2|9002| C++| hard       |60|2021-09-0106:00:00||3|9003| 算法   | medium     |80|2021-09-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|2021-09-0109:01:01|2021-09-0109:31:00|81||2|1002|9002|2021-09-0112:01:01|2021-09-0112:31:01|81||3|1003|9001|2021-09-0119:01:01|2021-09-0119:40:01|86||4|1003|9002|2021-09-0112:01:01|2021-09-0112:31:51|89||5|1004|9001|2021-09-0119:01:01|2021-09-0119:30:01|85||6|1005|9002|2021-09-0112:01:01|2021-09-0112:31:02|85||7|1006|9003|2021-09-0710:01:01|2021-09-0710:21:01|84||8|1006|9001|2021-09-0710:01:01|2021-09-0710:21:01|80|+----+------+---------+---------------------+---------------------+-------+

题目练习记录表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|1004|8001|2021-08-0219:38:01|70||6|1004|8002|2021-08-0219:48:01|90||7|1001|8002|2021-08-0219:38:01|70||8|1004|8002|2021-08-0219:48:01|90||9|1004|8002|2021-08-0219:58:01|94||10|1004|8003|2021-08-0219:38:01|70||11|1004|8003|2021-08-0219:48:01|90||12|1004|8003|2021-08-0119:38:01|80|+----+------+-------------+---------------------+-------+

请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。
结果按试卷完成数升序，按题目练习数降序。
示例数据输出如下：
+------+----------+--------------+| uid  | exam_cnt | question_cnt |+------+----------+--------------+|1001|1|2||1003|2|0|+------+----------+--------------+

解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；
1001完成了1次试卷1001，练习了2次题目；
1003完成了2次试卷9001、9002，未练习题目（因此计数为0）

🚀 建表语句
droptableifexists examination_info,user_info,exam_record,practice_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 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号',3100,7,'算法','2020-01-01 10:00:00'),(1002,'牛客2号',2300,7,'算法','2020-01-01 10:00:00'),(1003,'牛客3号',2500,7,'算法','2020-01-01 10:00:00'),(1004,'牛客4号',1200,5,'算法','2020-01-01 10:00:00'),(1005,'牛客5号',1600,6,'C++','2020-01-01 10:00:00'),(1006,'牛客6号',2000,6,'C++','2020-01-01 10:00:00');INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'SQL','hard',60,'2021-09-01 06:00:00'),(9002,'C++','hard',60,'2021-09-01 06:00:00'),(9003,'算法','medium',80,'2021-09-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),(1004,8001,'2021-08-02 19:38:01',70),(1004,8002,'2021-08-02 19:48:01',90),(1001,8002,'2021-08-02 19:38:01',70),(1004,8002,'2021-08-02 19:48:01',90),(1004,8002,'2021-08-02 19:58:01',94),(1004,8003,'2021-08-02 19:38:01',70),(1004,8003,'2021-08-02 19:48:01',90),(1004,8003,'2021-08-01 19:38:01',80);INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:31:00',81),(1002,9002,'2021-09-01 12:01:01','2021-09-01 12:31:01',81),(1003,9001,'2021-09-01 19:01:01','2021-09-01 19:40:01',86),(1003,9002,'2021-09-01 12:01:01','2021-09-01 12:31:51',89),(1004,9001,'2021-09-01 19:01:01','2021-09-01 19:30:01',85),(1005,9002,'2021-09-01 12:01:01','2021-09-01 12:31:02',85),(1006,9003,'2021-09-07 10:01:01','2021-09-07 10:21:01',84),(1006,9001,'2021-09-07 10:01:01','2021-09-07 10:21:01',80);

🍌🍌 答案
select er1.uid as uid,count(distinct er1.exam_id)as exam_cnt,count(distinct pr1.id)as question_cnt
from exam_record er1
leftjoin practice_record pr1 on er1.uid = pr1.uid andyear(er1.submit_time)=2021andYEAR(pr1.submit_time)=2021where er1.uid in(select er.uid
from user_info ui
leftjoin exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where  ei.tag ='SQL'and ui.level=7and difficulty ='hard'andyear(submit_time)=2021groupby ui.uid
havingavg(er.score)>80)groupby er1.uid
orderby exam_cnt , question_cnt desc;

在这里插入图片描述

🚀 SQL26 每个6/7级用户活跃情况

📖 user_info表结构
在这里插入图片描述
📖 examination_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号    |3100|7| 算法   |2020-01-0110:00:00||2|1002| 牛客2号    |2300|7| 算法   |2020-01-0110:00:00||3|1003| 牛客3号    |2500|7| 算法   |2020-01-0110:00:00||4|1004| 牛客4号    |1200|5| 算法   |2020-01-0110:00:00||5|1005| 牛客5号    |1600|6| C++|2020-01-0110:00:00||6|1006| 牛客6号    |2000|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|SQL| hard       |60|2021-09-0106:00:00||2|9002| C++| hard       |60|2021-09-0106:00:00||3|9003| 算法   | medium     |80|2021-09-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|2021-09-0109:01:01|2021-09-0109:31:00|81||2|1002|9002|2021-09-0112:01:01|2021-09-0112:31:01|81||3|1003|9001|2021-09-0119:01:01|2021-09-0119:40:01|86||4|1003|9002|2021-09-0112:01:01|2021-09-0112:31:51|89||5|1004|9001|2021-09-0119:01:01|2021-09-0119:30:01|85||6|1005|9002|2021-09-0112:01:01|2021-09-0112:31:02|85||7|1006|9003|2021-09-0710:01:01|2021-09-0710:21:01|84||8|1006|9001|2021-09-0710:01:01|2021-09-0710:21:01|80|+----+------+---------+---------------------+---------------------+-------+

题目练习记录表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|1004|8001|2021-08-0219:38:01|70||6|1004|8002|2021-08-0219:48:01|90||7|1001|8002|2021-08-0219:38:01|70||8|1004|8002|2021-08-0219:48:01|90||9|1004|8002|2021-08-0219:58:01|94||10|1004|8003|2021-08-0219:38:01|70||11|1004|8003|2021-08-0219:48:01|90||12|1004|8003|2021-08-0119:38:01|80|+----+------+-------------+---------------------+-------+

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、
2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：
+------+-----------------+---------------+--------------------+------------------------+| uid  | act_month_total | act_days_2021 | act_days_2021_exam | act_days_2021_question |+------+-----------------+---------------+--------------------+------------------------+|1001|2|2|1|1||1002|1|2|1|1||1003|1|1|1|0||1005|1|1|1|0||1006|1|1|1|0|+------+-----------------+---------------+--------------------+------------------------+

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，
2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

🚀 建表语句
droptableifexists examination_info,user_info,exam_record,practice_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 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号',3100,7,'算法','2020-01-01 10:00:00'),(1002,'牛客2号',2300,7,'算法','2020-01-01 10:00:00'),(1003,'牛客3号',2500,7,'算法','2020-01-01 10:00:00'),(1004,'牛客4号',1200,5,'算法','2020-01-01 10:00:00'),(1005,'牛客5号',1600,6,'C++','2020-01-01 10:00:00'),(1006,'牛客6号',2600,7,'C++','2020-01-01 10:00:00');INSERTINTO examination_info(exam_id,tag,difficulty,duration,release_time)VALUES(9001,'SQL','hard',60,'2021-09-01 06:00:00'),(9002,'C++','easy',60,'2021-09-01 06:00:00'),(9003,'算法','medium',80,'2021-09-01 10:00:00');INSERTINTO practice_record(uid,question_id,submit_time,score)VALUES(1001,8001,'2021-08-02 11:41:01',60),(1004,8001,'2021-08-02 19:38:01',70),(1004,8002,'2021-08-02 19:48:01',90),(1001,8002,'2021-08-02 19:38:01',70),(1004,8002,'2021-08-02 19:48:01',90),(1006,8002,'2021-08-04 19:58:01',94),(1006,8003,'2021-08-03 19:38:01',70),(1006,8003,'2021-08-02 19:48:01',90),(1006,8003,'2020-08-01 19:38:01',80);INSERTINTO exam_record(uid,exam_id,start_time,submit_time,score)VALUES(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:31:00',78),(1001,9001,'2021-09-01 09:01:01','2021-09-01 09:31:00',81),(1005,9001,'2021-09-01 19:01:01','2021-09-01 19:30:01',85),(1005,9002,'2021-09-01 12:01:01','2021-09-01 12:31:02',85),(1006,9003,'2021-09-07 10:01:01','2021-09-07 10:21:59',84),(1006,9001,'2021-09-07 10:01:01','2021-09-07 10:21:01',81),(1002,9001,'2020-09-01 13:01:01','2020-09-01 13:41:01',81),(1005,9001,'2021-09-01 14:01:01',null,null);

🍌🍌 答案
select t1.uid,count(distinct act_month) act_month_total,count(distinctcasewhenyear(act_day)=2021then act_day end) act_days_2021,count(distinctcasewhenleft(tag,1)=9andyear(act_day)=2021then act_day end) act_days_2021_exam,count(distinctcasewhenleft(tag,1)=8andyear(act_day)=2021then act_day end) act_days_2021_question
from user_info t1
leftjoin(select uid ,
      date_format(start_time,'%Y%m') act_month,
      date_format(start_time,'%Y%m%d') act_day,
      exam_id tag
from exam_record
unionallselect uid,
      date_format(submit_time,'%Y%m') act_month,
      date_format(submit_time,'%Y%m%d') act_day,
      question_id tag
from practice_record) t2
on t1.uid=t2.uid
where t1.uid in(select uid 
from user_info
wherelevelin(6,7))groupby uid
orderby act_month_total desc,act_days_2021 desc;

在这里插入图片描述

在这里插入图片描述

标签： sql 数据库 dba
本文转载自: https://blog.csdn.net/weixin_41645135/article/details/125126225
版权归原作者 IT邦德 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【牛客刷题-SQL进阶挑战】NO4.多表查询”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航