mysql 左连接 去重_210115:数据库左连接/右连接/内连接-Oracle中 NVL函数使用-Oracle去重的三个方法...

一. 数据库左连接、右连接、内连接

1. INNER JOIN (内连接)

内连接是一种一一映射关系，就是两张表都有的才能显示出来

用韦恩图表示是两个集合的交集，如图：

实现代码：

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

INNER JOIN table_b B

ON A.PK = B.PK;

查询结果：

2. LEFT JOIN (左连接)

左连接是左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分只能补空显示，所谓的左边表其实就是指放在left join的左边的表

用韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

LEFT JOIN table_b B

ON A.PK = B.PK;

查询结果：

3. RIGHT JOIN(右连接)

右连接正好是和左连接相反的，这里的右边也是相对right join来说的，在这个右边的表就是右表

用韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

RIGHT JOIN table_b B

ON A.PK = B.PK;

查询结果：

4. OUTER JOIN(外连接、全连接)

查询出左表和右表所有数据，但是去除两表的重复数据

韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

FULL JOIN table_b B

ON A.PK = B.PK;

上面代码在mysql执行是报错的，因为mysql不支持全连接，只能用以下代码实现效果，含义是左连接+右连接+去重=全连接：

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

LEFT JOIN table_b B

ON A.PK = B.PK

UNION

SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value

FROM table_a A

RIGHT JOIN table_b B

ON A.PK = B.PK;

查询结果：

5. LEFT JOIN EXCLUDING INNER JOIN(左连接不包含内连接)

这个查询是只查询左边表有的数据，共同有的也不查出来

韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK, A.Value AS A_Value,

B.Value AS B_Value, B.PK AS B_PK

FROM Table_A A

LEFT JOIN Table_B B

ON A.PK = B.PK

WHERE B.PK IS NULL

查询结果：

6. RIGHT JOIN EXCLUDING INNER JOIN(右连接不包含内连接)

这个查询是只查询右边表有的数据，共同有的也不查出来

韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK,

B.Value AS B_Value

FROM Table_A A

RIGHT JOIN Table_B B

ON A.PK = B.PK

WHERE A.PK IS NULL

查询结果：

7. OUTER JOIN EXCLUDING INNER JOIN(外连接不包含内连接)

意思就是查询左右表各自拥有的那部分数据

韦恩图表示如下：

实现代码：

SELECT A.PK AS A_PK, A.Value AS A_Value,

B.Value AS B_Value, B.PK AS B_PK

FROM Table_A A

FULL OUTER JOIN Table_B B

ON A.PK = B.PK

WHERE A.PK IS NULL

OR B.PK IS NULL

由于mysql不支持full join，只能通过下面代码模拟实现：

SELECT A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK,

B.Value AS B_Value

FROM table_a A

LEFT JOIN table_b B

ON A.PK = B.PK

WHERE B.PK IS NULL

UNION ALL

SELECT *

FROM table_a A

RIGHT JOIN table_b B

ON A.PK = B.PK

WHERE A.PK IS NULL;

查询结果：

二. Oracle中 NVL函数使用

NVL:如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例如：下面返回的是123

select nvl('123','456') from dual

又例如：下面返回的是456

select nvl(null,'456') from dual

其实可以把这个函数当作默认值的一个处理，例如上面的sql，可以理解为默认一个值为456

其他两个函数

NVL2:如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参

例如：下面返回222

select nvl2('111','222','333') from dual

又例如：下面返回333

select nvl2(null,'222','333') from dual

NULLIF:如果exp1和exp2相等则返回空(NULL)，否则返回第一个值

例如：下面返回一个空值

select NULLIF('111','111') from dual

又例如：下面返回111

select NULLIF('111','1111') from dual

而且两个值的数据类型要一致，不然会报错

三. Oracle去重的三个方法

1. distinct

在 SQL 中，关键字 distinct 用于返回唯一不同的值。其语法格式为：

SELECT DISTINCT 列名称 FROM 表名称

当 distinct 作用在多个字段的时候，她只会将所有字段值都相同的记录“去重”掉

关键字 distinct 只能放在 SQL 语句中所有字段的最前面才能起作用，如果放错位置，SQL 不会报错，但也不会起到任何效果。

2. row_number() over()

在 oracle数据库中，为咱们提供了一个函数 row_number() 用于给数据库表中的记录进行标号，在使用的时候，其后还跟着一个函数 over()，而函数 over() 的作用是将表中的记录进行分组和排序。两者使用的语法为：

ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

意为：将表中的记录按字段 COLUMN1进行分组，按字段 COLUMN2 进行排序，其中

PARTITION BY：表示分组ORDER BY：表示排序

从上面的结果可以看出，其在原表的基础上，多了一列标有数字排序的列。那么反过来分析咱们运行的 SQL 语句，发现其确实按字段 AGE 的值进行分组了，也按字段 NAME 的值进行排序啦！因此，函数的功能得到了验证。

接下来，咱们就研究如何用 row_number() over() 函数实现“去重”的功能。通过观察上面的结果，咱们可以发现，如果以 NAME 分组，以 AGE 排序，然后再取每组的第一个记录或许就可以实现“去重”的功能啊！那么试试看，运行如下 SQL 语句，

/*

* 其中 rn 表示最后添加的那一列

*/

select * from

(select PPPRDER.CESHIDEMO.*, row_number() over(partition by name order by age desc) rn from PPPRDER.CESHIDEMO)

where rn = 1

运行后，得到的结果如下所示：

3. group by

因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。

举例

比如说我们有一个学生表格(student)，包含学号(id)，课程(course)，分数(score)等等多个列，我们想通过查询得到每个学生选了几门课程，此时我们就可以联合使用COUNT函数与GROUP BY语句来得到这一结果

SELECT id, COUNT(course) as numcourse

FROM student

GROUP BY id

因为我们是使用学号来进行分组的，这样COUNT函数就是在以学号分组的前提下来实现的，通过COUNT(course)就可以计算每一个学号对应的课程数。

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

同样使用本文中的学生表格，如果想查询平均分高于80分的学生记录可以这样写：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错