mysql 三个表连表删除 mysql三张表连接

文章目录

• 问题概述
• 表连接概要
• 去重整体技术流程
• 参考资料

好记性不如烂笔头，此博文记录MySQL中表连接去重的一些技巧。

问题概述

在某业务中，材料表（material）中存储了商品编码和主要材料，品牌表（brand）中存储了商品编码、品牌名及售价。
需求：将材料表和品牌表结合成同一张表使得各个品牌和其主要材料一一对应。

• 建表语句

# 材料表
CREATE TABLE material (
  `id` int(11) NOT NULL,
  `material` varchar(60) DEFAULT NULL COMMENT '主要材料'
);
#品牌表
CREATE TABLE brand (
  `kid` int(11) NOT NULL,
  `brand_name` varchar(20) DEFAULT NULL COMMENT '品牌名',
  `price` double NOT NULL COMMENT '价格'
);

• 表字段说明

字段名	类型	描述
id	int	商品编码_材料表
material	varchar	主要材料_材料表

字段名	类型	描述
kid	int	商品编码_品牌表
brand_name	varchar	品牌名_品牌表
price	double	价格_品牌表

• 插入数据

# 为材料表插入数据
INSERT INTO material ( id, material )
VALUES
	( 1011, '巧克力' ),
	( 1012, '樱桃' ),
	( 1013, '黄油' ),
	( 1014, '荔枝' ),
	( 1015, '黄桃' );
# 为品牌表插入数据
INSERT INTO brand ( kid, brand_name, price )
VALUES
	( 1011, '巧克力派', 168 ),
	( 1012, '樱桃派', 175 ),
	( 1013, '黄油派', 237 ),
	( 1013, '黄油派', 237 ),
	( 1014, '荔枝派', 194 ),
	( 1015, '黄桃派', 139 ),
	( 1015, '黄桃派', 139 ),
	( 1015, '黄桃派', 139 );

插入的数据中，material表中共五条数据，其中并无重复数据；brand表中共八条数据其中编码1013有一条重复，编码1015的品牌有两条重复。

表连接概要

1. 为什么要进行表连接？
   在实际生产和业务中，符合业务需求的数据常存在于不同表中，这是因为大部分情况下，数据库的设计要符合规范，所有数据集中在一张表中会造成数据的冗余。从不同表中取数比较麻烦，表连接可解决这一问题
2. 表连接种类
   表连接种类可分为四类，分别为：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

• 内连接（inner join）
  内连接又称等值连接得出同时存在material表和brand表的数据集，即求两个表的交集。以商品编码为连接条件进行表的连接。

代码：

SELECT * FROM material INNER JOIN brand ON material.id = brand.kid;

结果：

从结果中可知，由于brand表中存在编码的重复记录，而重复品牌的商品编码也存在与material表中，最终连接的表数据记录为8条。

• 左连接(left [outer] join)
  左连接从左表(material)取出所有记录,与右表(brand)匹配。如果没有匹配，以null值代表右边表的列。outer 可以不写，默认情况下不写outer关键字。
  在左连接中左表material表写在left join前面是主表，因此左表的数据记录全部会出现在结果集中，而右表brand中的记录如果在主表中没找到匹配，就不会出现在结果集中。左连接的venn图如下图所示：
  
  如上图所示，左连接以左表作为主表，右表为从表。左连接上图中的两种情况左边代表在左连接时左表中的全部数据都出现在结果集中；右边的venn图表示结果集中只展示在左表中而不在右表的部分数据。
  代码：

SELECT	* FROM material LEFT JOIN brand ON material.id = brand.kid; # 左表中的全部数据

结果：

• 右连接(right [outer] join)
  右连接从右表(brand)取出所有记录,与左表(material)匹配。如果没有匹配，以null值代表左边表的列。outer 可以不写，默认情况下不写outer关键字。
  在右连接中右表brand表写在right join前面是主表，因此右表的数据记录全部会出现在结果集中，而左表material中的记录如果在主表中没找到匹配，就不会出现在结果集中。右连接的venn图如下图所示：
  
  如上图所示，右连接以右表作为主表，左表为从表。右连接上图中的两种情况左边代表在右连接时右表中的全部数据都出现在结果集中；右边的venn图表示结果集中只展示在右表中而不在左表的部分数据。
  代码：

SELECT	* FROM material RIGHT JOIN brand ON material.id = brand.kid;

结果：

• 全连接(full [outer] join)
  MySQL暂不支持这种语句，可以通过使用union将两个结果集“堆一起”，利用左连接，右连接分两次将数据取出，然后用union将数据合并去重。
  
  如上图所示，全连接上图中的两种情况左边代表在全连接时左右表的数据都出现在结果集中；右边的venn图表示结果集中展示左右表中不相交的数据。
  代码：

SELECT
	* 
FROM
	material
	LEFT JOIN brand ON material.id = brand.kid UNION
SELECT
	* 
FROM
	material
	RIGHT JOIN brand ON material.id = brand.kid;

结果：

在全连接结果图中采用union进行了去重的操作，故查询出的结果条数为5条。

• 交叉连接(cross join)-笛卡尔积
  如果A表有20条记录，B表有30条记录，则二者关联后的笛卡尔积工20*30=600条记实录。也就是说A表中的每条记录都会于B表的所有记录关联一次。
  代码：

SELECT * FROM material CROSS JOIN brand;

结果：

从上述结果图中看到交叉连接的结果为40条，即material表中的5条乘以brand表中的8条，material表中的每条记录与brand表中的所有记录都关联了一次。

去重整体技术流程

在上述的表连接查询结果示意图中，可以发现当连接的表中有重复记录时，表连接后也会显示对应重复的记录，这并不利于数据的整合和后续分析。接下来以左连接为例进行表连接去重探究
问题成因分析：

• 左连接时，左表(material)的记录会全部显示，而右表(brand)只会显示符合条件表达式的记录，如果在右表(brand)中没有符合条件的记录，则记录不足的地方为NULL。
• 使用left join， material表与brand表所显示的记录数为 1:1 或 1:0，material表中的所有记录都会显示，brand表只显示符合条件的记录。
• 但如果brand表符合条件的记录数大于1条，就会出现1:n的情况，这样left join后的结果，记录数会多于material表的记录数。

解决方法：

• 出发点：使material表与brand表所显示的记录数为 1:1对应关系。
• 方法：使用非唯一标识的字段做关联

• DISTINCT

SELECT DISTINCT material.id,
	material.material,
	brand.kid,
	brand.brand_name,
	brand.price 
FROM
	material
	LEFT JOIN brand ON material.id = brand.kid;

查询结果：DISTINCT是material表中唯一的数据重复的结果不显示

• GROUP BY & MAX

SELECT
	* 
FROM material
LEFT JOIN ( SELECT MAX( kid ) AS kid, MAX( brand_name ) AS brand_nanme, MAX( price ) AS price FROM brand GROUP BY kid ) AS b 
ON material.id = b.kid;

查询结果：

GROUP BY：拿出brand表的一条数据关联使material表与brand表所显示的记录数为 1:1对应关系；MAX：取唯一，拿出brand表的最后一条数据关联。

• UNION

SELECT
	* 
FROM
	material
	LEFT JOIN brand ON material.id = brand.kid UNION
SELECT
	* 
FROM
	material
	RIGHT JOIN brand ON material.id = brand.kid;

查询结果：