SQL语句拾遗

sql

前提

Tab1

Tab2

左连接

select * form tab1 left join tab2 on tab1.size = tab2.size

结果如下：

内连接

select * form tab1 inner join tab2 on tab1.size = tab2.size

结果如下：

右连接

select * form tab1 right join tab2 on tab1.size = tab2.size

结果如下：

普通where语句

select * form tab1, tab2 where tab1.size = tab2.size

结果如下：

全链接

select * form tab1 full join tab2 on tab1.size = tab2.size

结果如下：

结论

1、内连接和我们平时所用的where语句效果一致，即两个表的共同的部分
2、左连接，即已左边的表为主表，右边的表为副表，将主表中需要的字段全部列出，然后将副表中的数据按照on查询条件与其对应起来
3、右连接，与2相反
4、全连接，则是将两个表的需要的字段的数据全排列。

多表左连接

TAB3

我们来看一个sql语句

select * form tab1 
left join tab2 
on tab1.size = tab2.size
left join tab3
ont tab2.name = tab3.name

当多个表的时候，left join到底是怎么工作的？tab1是tab2的左表，然后tab2是tab3 的左表？还是tab1是左表，tab2和tab3都是右表？

结论：

tab1 左连接 tab2，以tab1为基础进行连接运算，得到新表即临时表（table）。
table 左连接 tab3， table为基础进行连接运算，得到最终结果。

关于on和where

我们再来看两个sql语句：

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条语句

第一步：
on条件: tab1.size = tab2.size，生成一张中间表

select * form tab1 left join tab2 on (tab1.size = tab2.size) 

结果如下：

第二步：
再对中间表 过滤where 条件：
tab2.name=’AAA’

where tab2.name=’AAA’

结果如下：

第二条语句

on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)

结果如下：

结论：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

有一个需要注意的：
左连接中，必然会返回左表的所有记录，而on之后，如果右表有重复的数据，例如tab2，如果符合on的条件，中间表就会增加数据；如果不符合on的条件，中间表中的右表相关数据就都为null，也就是说on只会增加数据，不会减少数据，除非用where

CASE

TAB4:

期望的结果如下：

这时，我们可以使用CASE语句，如下：

SELECT  SUM(population),   
            CASE country   
            WHEN '中国'     THEN '亚洲'   
            WHEN '印度'     THEN '亚洲'   
            WHEN '日本'     THEN '亚洲'   
            WHEN '美国'     THEN '北美洲'   
            WHEN '加拿大'  THEN '北美洲'   
            WHEN '墨西哥'  THEN '北美洲'   
            ELSE '其他' END   
            FROM    tab4   
            GROUP BY CASE country   
            WHEN '中国'     THEN '亚洲'   
            WHEN '印度'     THEN '亚洲'   
            WHEN '日本'     THEN '亚洲'   
            WHEN '美国'     THEN '北美洲'   
            WHEN '加拿大'  THEN '北美洲'   
            WHEN '墨西哥'  THEN '北美洲'   
            ELSE '其他' END； 

我们再来看一个例子：
TAB5

期望的结果如下：

当然，继续使用CASE，如下:

SELECT country,   
            SUM( CASE WHEN sex = '1' THEN   
            population ELSE 0 END),  --男性人口   
            SUM( CASE WHEN sex = '2' THEN   
            population ELSE 0 END)   --女性人口   
            FROM  tab5   
            GROUP BY country;  

group by

当有group by时，select后的字段，必须在gorup by里面或者在聚合函数里面。
先执行group by，然后执行聚合函数。

https://zhuanlan.zhihu.com/p/46869970

先来看下 TAB6：

FROM TAB6 Group BY name：该句执行后，我们想象生成了虚拟表，如下所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么第一行和第二行数据就合并成1行，所有的id值和number值写到一个单元格里面。

虚拟表如下：

接下来就要针对虚拟表执行Select语句了：

（1）如果执行select *的话，那么返回的结果应该是虚拟表，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以你看，执行select * 语句就报错了。

（2）我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

（3）那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

（4）例如我们执行select name,sum(number) sum from test group by name，那么sum就对虚拟表的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：

（5）group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。

count

https://www.jianshu.com/p/61b9ae271cdc

count(*)：所有行进行统计，包括NULL行
count(1)：所有行进行统计，包括NULL行
count(column)：对column中非Null进行统计

参考

多表联合查询(inner outer join 左右连接)详解
left join on和where条件的放置
探究SQL中Case语句用法