子查询与表连接

这一部分的数据集仍然沿用窗口函数的数据集，使用代码创建数据集如下（需要切换到sql server版本下）：

CREATE TABLE student
    ([student_id] int, 
     [name] varchar(2), 
     [grade] int, 
     [subject] varchar(10), 
     [score] int)
;

INSERT INTO student
    ([student_id], [name], [grade], [subject], [score])
VALUES
    (101, 'A', 1, 'Math', 70),
    (101, 'A', 1, 'Chinese', 80),
    (101, 'A', 1, 'English', 60),
    (102, 'B', 2, 'Math', 75),
    (102, 'B', 2, 'Chinese', 90),
    (102, 'B', 2, 'English',55)
;

子查询

子查询是工作中非常常用的，常用的原因是数据分析师的数据查询往往是一次运行直接输出查询结果，不会运行多步、借助临时表等进行查询，所以就使得子查询的应用非常广泛。子查询是什么，实际就是嵌套查询，也就是将查询出的结果再放到一个查询中去。子查询的类型有很多种，由于很多子查询工作中几乎用不到，在这里就不展开论述，只说一下最为常用的两个：where子查询和from子查询。

where子查询

我们都知道，where解决的问题是筛选，筛选就需要有判断条件，有时候判断条件的数据不是直接可以得到的，比方说平均数，当前可以首先查询平均数，然后再去筛选大于平均数的记录，这样太麻烦了，一个简单的方式就是在where中引入子查询。

还是以学生成绩为例，想筛选语文这个科目下的未达到平均分的学生名单，按照简单的思路应该是首先查询语文这个科目的平均分，然后再把平均分复制，然后再次查询。

--第一步 查询平均分
select 
    avg(score) as score_avg
from student
where subject = 'Chinese'
--第二步 按照平均分筛选学生姓名
select 
    name
from student
where subject='Chinese' and score < 85

子查询实际就是把第一步的查询放到第二步查询中，形成一次查询出结果，代码如下

select
    name
from student
where 
    subject='Chinese' 
    and score < (select avg(score) from student where subject = 'Chinese')

很容易理解，就是把刚才复制平均分查询结果的步骤省略，直接使用子查询代替。

from子查询

from子查询是工作中最常用的子查询方式，原因就是数据表的内容不是直接想要的数据，还需要进行处理，而且要一次运行出结果，所以from子查询就特别常用。

from子查询其实就是多步查询，一步步的嵌套，理解from子查询的关键就是把查询出来的结果当做一个数据表进行再次查询，这样就能够很快的掌握from子查询的诀窍。

现在想查询各科成绩中分数最高的学生姓名和成绩。一看这个查询需求就知道需要使用row_number先给各科目中学生的分数一个序号，然后再筛选序号为1的学生就好了。容易理解的方法如下：

--第一步：先给各个科目下的学生成绩按照降序排序，并给出一个序号。注意下述代码在左侧框内输入，且MySQL创建表的方法与SQL Server存在差异，在这里直接使用into写入表table2。
select
    name,
    subject,
    score,
    row_number() over(partition by subject order by score desc) as score_rank
into table2
from student
--第二步：筛选出序号为1的学生姓名
select 
    name,
    subject,
    score
from table2
where score_rank=1

从目前了解的大厂的数据分析师来看，几乎不存在新建表，然后再查询的操作，数据存储平台的资源肯定支撑不了这种操作，所以需要一步查询出结果。在上面代码中我使用了table2这个不存在的表来指代第一次查询出的结果，from子查询就是融合两步的内容，合并成一段代码如下：

select 
    name,
    subject,
    score
from 
    (
        select
            name,
            subject,
            score,
            row_number() over(partition by subject order by score desc) as score_rank
    from student
    ) as table2
where score_rank=1

上述代码的运行结果如下：

理解子查询的关键点就是把查询出来的数据当成一个新的数据表来进行再次查询。理解了这一点就能帮助我们在工作中梳理清楚查询的逻辑，方便写出正确的代码。在这里我又一次使用了as，是重命名的意思，将查询出来的表重命名为一个表名，方便查询。

灵活使用from子查询的关键就是SQL查询出来的结果是一个数据表，可以基于该数据表进行再次查询。可以由想要的查询结果出发，思考应该如何逐步查询出相应的结果。

select子查询

select子查询，顾名思义，就是将查询的结果进行再次查询。还是以建立的数据表为例，想查询英语分数及格的占所有有英语分数的比例。按照正常的查询流程应该如下

--查询英语及格的人数
select
    count(name) as stu_num1
from student
where subject = 'English' and score >= 60
--查询有英语成绩的人数
select
    count(name) as stu_num2
from student
where subject = 'English'
--最后做除法计算比例
stu_num1/stu_num2

上述计算方法太复杂，可以直接使用select子查询将结果查询出来

select 
(select count(name) as stu_num1 from student where subject = 'English' and score >= 60)/
cast((select count(name) as stu_num2 from student where subject = 'English') as float)

在这里使用了一个sql server的函数cast，其作用是转换数据类型，(select count(name) as stu_num2 from student where subject = 'English')查询出的结果是一个整数，sql server的整数除法结果仍然是一个整数，会自动忽略小数位，所以在这里使用cast将整数转化为浮点数(float)，这样计算结果就可以是浮点数。

输出结果是0.5。

having子查询

having子查询与where子查询非常像，where与having的区别在前面也已经介绍过了，在这里不再赘述。以数据集为例，现在想统计平均分最高的学科及其分数。正常的计算逻辑是，先把学科的最高平均分计算出来，然后再做一次选择。

select
    max(a.score_avg)
from
(
    select
      subject,
      avg(score) as score_avg
    from student 
    group by subject
)a
;

输出结果是85，在这里使用了一个from子查询确定最大的平均分。

--选择平均分最高的学科
select
  subject,
  avg(score) as score_avg
from student 
group by subject
having avg(score) = 85;

自然就可以选择出学科"Chinese"。

这样分两段写比较麻烦，可以使用having子查询直接进行过滤。

select
  subject,
  avg(score) as score_avg
from student 
group by subject
having avg(score) = 
        (select
            max(a.score_avg)
         from
         (
             select
             subject,
             avg(score) as score_avg
             from student 
             group by subject
         )a
        );

就是把avg(score)后面的数字替换为查询的公式。

表连接

数据查询不可能是单表查询，多表查询就涉及到表连接。表的连接分为两种：横向表连接和纵向表连接。横向表连接解决的是通过一个或多个字段，将不同表中的字段合并到一张表中用于后续分析；纵向表连接是把数据进行堆叠，是把具有相同字段（包含字段名、变量类型和字段顺序）的数据表堆叠成一张表。

纵向表连接

主要语句是union和union all。两者的区别是union不允许重复数据，union all则允许重复数据。举个例子说明，学生表的数据被分别放在了两个数据表中，分别命名为student1和student2。

CREATE TABLE student1
    ([student_id] int, 
     [name] varchar(2), 
     [grade] int, 
     [subject] varchar(10), 
     [score] int)
;

INSERT INTO student1
    ([student_id], [name], [grade], [subject], [score])
VALUES
    (101, 'A', 1, 'Math', 70),
    (101, 'A', 1, 'Chinese', 80),
    (101, 'A', 1, 'English', 60)
;
CREATE TABLE student2
    ([student_id] int, 
     [name] varchar(2), 
     [grade] int, 
     [subject] varchar(10), 
     [score] int)
;

INSERT INTO student2
    ([student_id], [name], [grade], [subject], [score])
VALUES
    (102, 'B', 2, 'Math', 75),
    (102, 'B', 2, 'Chinese', 90),
    (102, 'B', 2, 'English',55)
;

现在需要获取全部学生名单，代码如下

select
    *
from student1

union all

select 
    *
from student2

当两张表的字段名称和顺序不一致时，则可以在查询按照想要的顺序进行select，同时借助使用as对字段进行重命名，从而实现名称一致。

横向表连接

数据分析师工作中，不可能只围绕单独的表进行查询，在做分析时，肯定需要多张表的联合查询。这时候就需要使用横向表连接。为方便理解，在这里，以学生和家长的对应关系为例，一名学生可以有多名家长，一名家长也可以有多名在校学生。两张表的结构如下：

学生表

家长表

使用MySQL生成上述数据表的代码如下：

CREATE TABLE student
    (`student_name` varchar(1), `grade` int)
;

INSERT INTO student
    (`student_name`, `grade`)
VALUES
    ('A', 1),
    ('B', 2),
    ('C', 1),
    ('D', 1),
    ('E', 2),
    ('F', 1)
;


CREATE TABLE parents
    (`student_name` varchar(1), `parents_name` varchar(2))
;

INSERT INTO parents
    (`student_name`, `parents_name`)
VALUES
    ('A', 'A1'),