【Mysql教程】SQL多表多字段比对代码

所需工具：

Mysql

聪明的大脑

勤劳的双手

注意：本站只提供教程，不提供任何成品+工具+软件链接，仅限用于学习和研究，禁止商业用途，未经允许禁止转载/分享等

介绍

表-表比较
整体思路
找出不同字段的明细
T1/T2两表ID相同的部分，是否存在不同NAME
两表的交集与差集：判断两表某些字段是否相同
两表的交集与差集：找出T2表独有的id
字段-字段比较
判断两个字段间一对多或多对一的关系
证明id字段不是主键
证明id, name字段不是联合主键
数据准备

教程如下

表-表比较

整体思路

两张表条数一样

条数相同是前提，然后比较字段值才有意义

两表字段值完全相同【两表所有字段的值相同】

两表所有字段union后，条数与另一张表条数一样

两表字段值部分相同【两表部分字段的值相同】

原理：union有去重功能
两表部分字段union后，条数与另一张的count(distinct 部分字段)一样

找出不同字段的明细

找出不同字段的明细

T1/T2两表ID相同的部分，是否存在不同NAME

 	SELECT T1.ID,T2.ID,T1.`NAME`,T2.`NAME`
 	FROM A T1
 	LEFT JOIN B T2
 	ON T1.ID = T2.ID
 	AND COALESCE(T1.ID,'') <> ''
 	AND COALESCE(T2.ID,'') <> ''
 	WHERE T1.`NAME` <> T2.`NAME`;

两表的交集与差集：判断两表某些字段是否相同

判断两表某些字段是否相同，3种查询结果相同

 	-- 写法01
 	SELECT COUNT(1) FROM (
 	SELECT DISTINCT ID,`NAME` FROM A
 	) T1;
 	-- 写法02
 	SELECT COUNT(1) FROM (
 	SELECT DISTINCT ID,`NAME` FROM B
 	) T2;
 	-- 写法03
 	SELECT COUNT(1) FROM (
 	SELECT DISTINCT ID,`NAME` FROM A
 	UNION
 	SELECT DISTINCT ID,`NAME` FROM B
 	) T0;

not in与exists

两表的交集与差集：找出T2表独有的id

找出只存在于T2，不在T1中的那些id

下面2种写法结果一样

 	-- 写法01
 	SELECT T2.`NAME`,T2.* FROM A T2 WHERE T2.`NAME` IS NOT NULL
 	AND NOT EXISTS (SELECT 1 FROM B T1 WHERE T1.ID = T2.ID);
 	-- 写法02
 	SELECT T2.`NAME`,T2.* FROM A T2 WHERE T2.`NAME` IS NOT NULL
 	AND T2.ID NOT IN (SELECT T1.ID FROM B T1 );

字段-字段比较

判断两个字段间一对多或多对一的关系

测试id与name的一对多关系以下SQL会报错，报错原因 GROUP BY

 	SELECT ID,`NAME`,COUNT(*)
 	FROM A
 	GROUP BY ID
 	HAVING COUNT(`NAME`)>1;

修改后：

 	SELECT ID, COUNT(DISTINCT `NAME`)
 	FROM A
 	GROUP BY ID
 	HAVING COUNT(DISTINCT `NAME`)>1;

这样就说明id与name是一对多的关系

扩展：多对多关系，上述SQL中id与name位置互换后，查询有值，就说明两者是多对多关系

证明id字段不是主键

下面2种写法结果一样

 	-- 写法01
 	SELECT ID
 	FROM A
 	GROUP BY ID
 	HAVING COUNT(*)>1;
 	-- 写法02
 	SELECT ID,COUNT(ID)
 	FROM A
 	GROUP BY ID
 	HAVING COUNT(ID)>1;

证明id, name字段不是联合主键

 	SELECT ID,`NAME`
 	FROM A
 	GROUP BY ID,`NAME`
 	HAVING COUNT(*)>1
 	ORDER BY ID;

数据准备

 	-- 建表
 	CREATE TABLE IF NOT EXISTS TEST01.A
 	(
 	         ID VARCHAR(50) COMMENT 'ID号' -- 01
 	     ,NUMS INT COMMENT '数字' -- 02
 	     ,NAME VARCHAR(50) COMMENT '名字' -- 03
 	 
 	)
 	COMMENT 'A表'
 	STORED AS PARQUET
 	;
 	 
 	-- 插数
 	INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('01',1,NULL);
 	INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('02',2,'');
 	INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('03',3,'c');
 	 
 	-- 删数
 	DELETE FROM TEST01.A WHERE ID = '04';
 	-- 删表
 	DROP TABLE IF EXISTS TEST01.A;