当前位置:   article > 正文

MySQL 嵌套子查询 with子句 from子查询 in子查询 join子查询_mysql 嵌套查询

mysql 嵌套查询

一、适用场景和方法

(1)适用场景

考虑查询过程中是否存在以下情况:

  • 查询某些数据时需要分组才能得到,某些数据不需要分组就能得到或者分组条件不同;
  • 查询某些数据时需要where条件,某些列不需要where条件。

存在上述情况时,可能会用到子查询,不论是否涉及到子查询的问题,我们都可以把复杂查询问题简单化,拆解为简单问题,一一解决。

(2)方法

MySQL嵌套子查询,可以在 with、from、in、join 中使用。

什么是子查询?
子查询可以放在select语句的where条件中、having条件中、from子句中。

子查询分为哪几类?

  • 单行子查询:where 条件中使用 <>、<、>、<=、>=、=;having子句中使用 <>、<、>、<=、>=、=(其中子查询得到的是一个值);在from子句中也可以使用子查询
    注意: 单行子查询不能包含order by 子句

  • 多行子查询:可以向外部的SQL语句返回一行或者多行记录。外部查询可以使用in、any、all。在子查询中使用any操作符之前,必须使用一个=、<>、<、>、<=、>=;在子查询中使用all操作符之前,必须使用一个=、<>、<、>、<=、>=>

什么是嵌套子查询?
子查询里面还有一个子查询

二、案例分析

下面用3个具体的案例(由简到难)来说明子查询和嵌套子查询的求解过程:

(1)简单案例

案例来自:SQL131 作答试卷得分大于过80的人的用户等级分布
描述
现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):

iduidnick_nameachievementleveljobregister_time
11001牛客1号31007算法2020-01-01 10:00:00
21002牛客2号21006算法2020-01-01 10:00:00
31003牛客3号15005算法2020-01-01 10:00:00
41004牛客4号11004算法2020-01-01 10:00:00
51005牛客5号16006C++2020-01-01 10:00:00
61006牛客6号30006C++2020-01-01 10:00:00

试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

试卷作答信息表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:41:0179
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-01 12:01:012021-09-01 12:31:0170
4100290012021-09-01 19:01:012021-09-01 19:40:0180
5100290032021-08-01 12:01:012021-08-01 12:21:0160
6100290022021-09-01 12:01:012021-09-01 12:31:0170
7100290012021-09-01 19:01:012021-09-01 19:40:0185
8100290022021-09-01 12:01:01(NULL)(NULL)
9100390032021-09-07 10:01:012021-09-07 10:31:0186
10100390032021-09-08 12:01:012021-09-08 12:11:0140
11100390012021-09-01 13:01:012021-09-01 13:41:0181
12100390022021-09-01 14:01:01(NULL)(NULL)
13100390032021-09-08 15:01:01(NULL)(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
15100590022021-09-01 12:01:012021-09-01 12:31:0188
16100690022021-09-01 12:11:012021-09-01 12:31:0189

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

levellevel_cnt
62
51

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

【分类】:嵌套子查询、多表连接

分析思路

难点:
1.计算不同试卷类型的用户人数要记得去重
2.如何实现 in 嵌套子查询

(1)统计作答SQL类别的试卷
​ [条件]:where tag = ‘SQL’ score > 80

(2)统计作答SQL类别的试卷得分大于过80的人
​ [条件]:where score > 80 and exam_id in 。。。

(3)统计作答SQL类别的试卷得分大于过80的人的用户等级分布,按数量降序排序
​ [使用]:group by level;order by level_cnt desc

最终结果

select 查询结果 [等级;等级分布]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [SQL类别试卷;得分大于过80的人]
group by 分组条件 [等级]
order by 对查询结果排序 [等级分布数量降序、等级降序];
  • 1
  • 2
  • 3
  • 4
  • 5
求解代码

方法一:

嵌套子查询+in 子查询

select
    level,
    count(level) as level_cnt
from user_info
where uid in(
    #统计作答SQL类别的试卷得分大于过80的人
    select
        uid
    from exam_record
    where score > 80
    and exam_id in(
        #统计作答SQL类别的试卷
        select 
            exam_id
        from examination_info
        where tag = 'SQL'
        )
    )
group by level
order by level_cnt desc;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

方法二:
多表连接

select
    level,
    count(distinct uid) as level_cnt
from user_info
join exam_record using(uid)
join examination_info using(exam_id)
where tag = 'SQL'
and score > 80
group by level
order by level_cnt desc,level desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

131附录:创建示例表的代码:

drop table if exists examination_info;
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;

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;

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 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');

INSERT INTO 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');

INSERT INTO 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);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61

(2)中等案例

案例来自:SQL130 试卷发布当天作答人数和平均分
描述
现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间),示例数据如下:

iduidnick_nameachievementleveljobregister_time
11001牛客1号31007算法2020-01-01 10:00:00
21002牛客2号21006算法2020-01-01 10:00:00
31003牛客3号15005算法2020-01-01 10:00:00
41004牛客4号11004算法2020-01-01 10:00:00
51005牛客5号16006C++2020-01-01 10:00:00
61006牛客6号30006C++2020-01-01 10:00:00

释义:用户1001昵称为牛客1号,成就值为3100,用户等级是7级,职业方向为算法,注册时间2020-01-01 10:00:00

试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间) 示例数据如下:

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分) 示例数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:012021-09-01 09:41:0170
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-02 12:01:012021-09-02 12:31:0170
4100290012021-09-01 19:01:012021-09-01 19:40:0180
5100290032021-08-01 12:01:012021-08-01 12:21:0160
6100290022021-08-02 12:01:012021-08-02 12:31:0170
7100290012021-09-01 19:01:012021-09-01 19:40:0185
8100290022021-07-06 12:01:01(NULL)(NULL)
9100390022021-09-07 10:01:012021-09-07 10:31:0186
10100390032021-09-08 12:01:012021-09-08 12:11:0140
11100390032021-09-01 13:01:012021-09-01 13:41:0170
12100390012021-09-08 14:01:01(NULL)(NULL)
13100390022021-09-08 15:01:01(NULL)(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
15100590022021-09-01 12:01:012021-09-01 12:31:0188
16100590022021-09-02 12:11:012021-09-02 12:31:0189

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

exam_iduvavg_score
9001381.3

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

【分类】:嵌套子查询、多表连接

分析思路

难点:
1.实现with子句、from子查询和in子查询时,先查询试卷类型还是用户?

(1)统计SQL类别试卷发布后,当天有作答记录的试卷

  • [条件]:where date_format(release_time,‘%Y%m%d’) = date_format(submit_time,‘%Y%m%d’) and tag = ‘SQL’

(2)统计SQL类别试卷发布后,当天有作答记录的试卷对应的5级以上的用户

  • [条件]:where level>5

(3)统计每张SQL类别试卷发布后,当天5级以上的用户作答的人数uv和平均分avg_score,按人数降序,相同人数的按平均分升序

  • [使用]:group by exam_id;order by uv desc,avg_score

最终结果

select 查询结果 [试卷ID;作答人数;平均成绩]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [SQL类别试卷;发布当天的5级用户]
group by 分组条件 [试卷ID]
order by 对查询结果排序 [按人数降序、相同人数的按平均分升序];
  • 1
  • 2
  • 3
  • 4
  • 5
求解代码

方法一:
嵌套子查询 + in 子查询

select
    exam_id,
    count(distinct uid) as uv,
    round(avg(score),1) as avg_score
from exam_record
where uid in(
    #统计SQL类别试卷发布后,当天有作答记录的试卷对应的5级以上的用户
    select
        uid
    from user_info
    where level > 5
    and exam_id in(
        #统计SQL类别试卷发布后,当天有作答记录的试卷
        select
            exam_id
        from examination_info
        join exam_record using(exam_id)
        where date_format(release_time,'%Y%m%d') = date_format(submit_time,'%Y%m%d') and tag = 'SQL'
        )  
    )
group by exam_id
order by uv desc,avg_score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

方法二:

  • with 子句
with
    main as(
        #统计SQL类别试卷发布后,当天有作答记录的试卷
        select 
            uid,
            exam_id,
            score
        from examination_info
        join exam_record using(exam_id)
        where date_format(release_time,'%Y%m%d') = date_format(submit_time,'%Y%m%d')
        and tag = 'SQL'
    ),
    main1 as(
        #统计SQL类别试卷发布后,当天有作答记录的试卷对应的5级以上的用户
        select 
            score,
            uid,
            exam_id
        from main join user_info using(uid)
        where level>5
    )
#统计每张SQL类别试卷发布后,当天5级以上的用户作答的人数uv和平均分avg_score,按人数降序,相同人数的按平均分升序
select 
    exam_id,
    count(distinct uid) as uv,
    round(avg(score),1) as avg_score
from main1
group by exam_id
order by uv desc,avg_score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • with 子句 + join
with
    main as(
        #统计SQL类别试卷发布后,当天有作答记录的试卷对应的5级以上的用户
        select 
            uid,
            exam_id,
            score
        from examination_info
        join exam_record using(exam_id)
        join user_info using(uid)
        where date_format(release_time,'%Y%m%d') = date_format(submit_time,'%Y%m%d')
        and tag = 'SQL'
        and level > 5
    )
#统计每张SQL类别试卷发布后,当天5级以上的用户作答的人数uv和平均分avg_score,按人数降序,相同人数的按平均分升序
select 
    exam_id,
    count(distinct uid) as uv,
    round(avg(score),1) as avg_score
from main
group by exam_id
order by uv desc,avg_score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

方法三:
多表连接

select
    exam_id,
    count(distinct uid) as uv,
    round(avg(score),1) as avg_score
from exam_record
join user_info using(uid)
join examination_info using(exam_id)
where tag = 'SQL'
and date_format(release_time, '%Y%m%d') = date_format(start_time, '%Y%m%d') 
and level > 5
group by exam_id
order by uv desc,avg_score
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

130附录:创建示例表的代码:

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号', 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');

INSERT INTO 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');

INSERT INTO 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);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59

(3)较难案例

案例来自:SQL 129月均完成试卷数不小于3的用户爱作答的类别
描述
现有试卷作答记录表exam_record(uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间,没提交的话为NULL, score:得分),示例数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:01(NULL)(NULL)
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-02 12:01:012021-09-02 12:31:0170
4100290012021-09-05 19:01:012021-09-05 19:40:0181
5100290022021-07-06 12:01:01(NULL)(NULL)
6100390032021-09-07 10:01:012021-09-07 10:31:0186
7100390032021-09-08 12:01:012021-09-08 12:11:0140
8100390012021-09-08 13:01:01(NULL)(NULL)
9100390022021-09-08 14:01:01(NULL)(NULL)
10100390032021-09-08 15:01:01(NULL)(NULL)
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100590022021-09-01 12:01:012021-09-01 12:31:0188
13100590022021-09-02 12:11:012021-09-02 12:31:0189

试卷信息表examination_info(exam_id:试卷ID, tag:试卷类别, difficulty:试卷难度, duration:考试时长, release_time:发布时间),示例数据如下:

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

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

tagtag_cnt
C++4
SQL2
算法1

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

【分类】:子查询、多表连接

分析思路

难点:
1.统计出“当月均完成试卷数”不小于3的用户们,with子查询和from子查询一定要去重,in子查询不用去重
2.多表做连接

(1)统计出“当月均完成试卷数”不小于3的用户们
​ [条件]:where submit_time is not null
​ [使用]:distinct。一定要去重,要不然做连接之后作答次数会计算错误。in不用

(2)统计用户们爱作答的类别及作答次数,按次数降序输出
​ [条件]:where a.answer_cnt < 3 and upper(a.tag)!= a.tag。
​ [使用]:多表连接使用 join using( )

最终结果

select 查询结果 [爱作答的类别;作答次数]
from 从哪张表中查询数据[多个join连接的表]
group by 分组条件 [类别]
order by 对查询结果排序 [按次数降序];
  • 1
  • 2
  • 3
  • 4
求解代码

方法一:
with 子句

with
    main as(
        #统计出“当月均完成试卷数”不小于3的用户们
        select distinct
            uid
        from exam_record
        join examination_info using(exam_id)
        where submit_time is not null
        group by uid,month(start_time) having count(score)>=3
    )
#统计用户们爱作答的类别及作答次数,按次数降序输出
select
    tag,
    count(start_time) as tag_cnt
from main
join exam_record using(uid)
join examination_info using(exam_id)
group by tag
order by tag_cnt desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

方法二
from 子查询

select
    tag,
    count(start_time) as tag_cnt
from (
    #统计出“当月均完成试卷数”不小于3的用户们
    select distinct
        uid
    from exam_record
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
) main
join exam_record using(uid)
join examination_info using(exam_id)
group by tag
order by tag_cnt desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

方法三
join 子查询

select
    tag,
    count(start_time) as tag_cnt
from exam_record
join examination_info using(exam_id)
join(
    #统计出“当月均完成试卷数”不小于3的用户们
    select distinct
        uid
    from exam_record
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
    )main using(uid)
group by tag
order by tag_cnt desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

方法四
in 子查询

select 
    tag,
    count(start_time) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in(
    #统计出“当月均完成试卷数”不小于3的用户们
    select
        uid
    from exam_record
    join examination_info using(exam_id)
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
)
group by tag
order by tag_cnt desc
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

附录:创建示例表的代码:

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;

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 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');

INSERT INTO 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);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

三、总结

只要理清求解思路写出任一种查询方法,都可以迁移到另外的方法中。
适用于所有复杂查询问题的求解方法:拆分成最小问题,逐个解决,再合并。所以一定要掌握with子句、join、from、in这几个最常用的方法。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/花生_TL007/article/detail/498771
推荐阅读
相关标签
  

闽ICP备14008679号