SQL 两表连接需要注意避的雷：连接键是不是需要GROUP BY ？

我们经常使用联结关键字，来联结各个表，联结问题，如何选取联结键是个重点，需要引起我们读者的注意：

能成为联结键的条件：
（1）该字段必须两张表都存在
（2）该字段必须为这两张表的“”“主键”，（这个主键之所以加引号，是因为该字段必须和主键一样，成为这张表的粒度。即该字段的某一个值只对应一条数据！如果他不能成为这样的键，那就必须把它改造成这样的键）

这么说，可能有点抽象。大家都知道身份证号吧。一个身份证号如果他存在的haul，是不是只对应一个人。如果有两个人对应着同一个身份证号，那么就证明其中一个人是假的。在数据库中，我们习惯将这种履行“身份证号”功能的成为主键。但是如果我们选取主键其他键来作为连接键呢。怎么让一个可能对应多条数据的字段值变成只对应一条数据的字段值呢？ 答案就是 GROUP BY

GROUP BY 的功能不只在于它能分组，还有一个功能就是，被GROUP BY 的字段会变成“主键”那样，一个字段值只对应一条数据，那就是对他的汇总值。

eg:
table1（学生成绩表）

姓名 考试成绩 学号
小明 80 101
小明 80 101
小明 100 101
小红 90 102
小红 90 102
小红 90 102

table2（学生信息表）

姓名 学号 详细信息
小明 101 1班学生（家住A市B区）
小红 102 1班学生（家住A市C区）

这是小明和小红分别参加三次考试的学生成绩表和存储小明和小红的详细信息的学生信息表。如果我想让两者表通过学号联结呢？

table1里面每个人都有三条记录。此时学号并不是主键！因为此时每个人都有三次记录表，
select count(1) from table1 where 学号=‘101’
结果会等于3

但是table2 里面 学号就是主键（也是该表的粒度）。

那么我此时不能贸然
table1 join table2
on table1.学号=table2.学号。
这时会犯大错（sql不会报错，但是自身逻辑出错！）
比如我count（1）想计算有多少人参加考试
大家想想会是几？

其实，结果为6

本来就两个人参加的考试，结果为6，这样会多算多少人！

所以，大家在选取联结键时，一定要仔细考虑，该字段是不是在两张表都是“主键”

此时大家可以参考我的代码：

with 
	cte1 as (
        select
            学号,
            姓名,
            sum(考试成绩) as ava_score
        from table1
        group by 学号 ,姓名
    ),
    cte2 as (
        select * from table2
    )

select
        cte1.学号,
        cte1.姓名,
        ava_score,
        详细信息
from cte1 join  cte2
    on cte1.学号= cte2.学号 and cte1.姓名=cte2.姓名

表1 中的被 GROUP BY 后的学号 就变成的“主键”

此时再count(1) 结果 就是 2。

那么有人会纳闷 ，为什么结果为6 ？

未group by 时，两者join时，会是这样
姓名 考试成绩 学号 详细信息

小明 80 101 1班学生（家住A市B区）
小明 80 101 1班学生（家住A市B区）
小明 100 101 1班学生（家住A市B区）
小红 90 102 1班学生（家住A市C区）
小红 90 102 1班学生（家住A市C区）
小红 90 102 1班学生（家住A市C区）

不信，我们可以来验证一下：
我从这样表中取医院id 医院name

select
            hospital_id, hospital_name
        from  x.xxx
        where hospital_id='66139'

证明这样表中，有三条这样的数据

再从这个数据表中取orgid orgname(等同于医院id 医院name)

 select
            orgid,orgname
        from x.xxx
        where project ='xxx'and  etl_source='xxx' and orgid='66139'
        group by orgid,orgname

这张表只有一条这样的数据

此时，我将这两表联结：

with
    cte1 as (
        select
            hospital_id, hospital_name
        from  x.xxx
        where hospital_id='66139'
),
    cte2 as (
        select
            orgid,orgname
        from x.xxx
        where project ='xxx'and  etl_source='xxx' and orgid='66139'
        group by orgid,orgname
)


select
    hospital_id,
    orgid,
    hospital_name,
    orgname
from cte1 join cte2
    on cte1.hospital_id=cte2.orgid 
    and cte1.hospital_name=cte2.orgname

执行结果为：

有三条数据

所以大家在联结的时候，一定要想清楚：
这个联结键能不能直接join，如果不能，先group by 进行汇总

在大数据的今天，group by 还能提高效率。