SQL多表多字段比对方法实例代码

 

表-表比较

整体思路

  • 两张表条数一样
    • 条数相同是前提,然后比较字段值才有意义
  • 两表字段值完全相同【两表所有字段的值相同】
    • 两表所有字段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;

 

总结

关于SQL多表多字段比对方法的文章就介绍至此,更多相关SQL多表多字段比对内容请搜索编程宝库以前的文章,希望以后支持编程宝库

 1.连接查询作用:当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回 2、连接类型内连接定义:内连接查询:查询结果为两个表匹配到的数据语法1:select * f ...