快捷導(dǎo)航

SQL多表多字段比對(duì)方法實(shí)例代碼

更新時(shí)間：2022年05月18日 14:34:09 作者：了毒蛇粉的貓

有時(shí)候正式庫(kù)和測(cè)試庫(kù)同一個(gè)表有字段有差異,會(huì)造成各種錯(cuò)誤,下面這篇文章主要給大家介紹了關(guān)于SQL多表多字段比對(duì)方法的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

表-表比較

整體思路

兩張表?xiàng)l數(shù)一樣
- 條數(shù)相同是前提，然后比較字段值才有意義
兩表字段值完全相同【兩表所有字段的值相同】
- 兩表所有字段union后，條數(shù)與另一張表?xiàng)l數(shù)一樣
兩表字段值部分相同【兩表部分字段的值相同】
- 原理：union有去重功能
- 兩表部分字段union后，條數(shù)與另一張的count(distinct 部分字段)一樣
找出不同字段的明細(xì)

找出不同字段的明細(xì)

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種查詢(xún)結(jié)果相同

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

not in與exists

兩表的交集與差集：找出T2表獨(dú)有的id

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

下面2種寫(xiě)法結(jié)果一樣

-- 寫(xiě)法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);
-- 寫(xiě)法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 );

字段-字段比較

判斷兩個(gè)字段間一對(duì)多或多對(duì)一的關(guān)系

測(cè)試id與name的一對(duì)多關(guān)系以下SQL會(huì)報(bào)錯(cuò)，報(bào)錯(cuò)原因 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;

這樣就說(shuō)明id與name是一對(duì)多的關(guān)系

擴(kuò)展：多對(duì)多關(guān)系，上述SQL中id與name位置互換后，查詢(xún)有值，就說(shuō)明兩者是多對(duì)多關(guān)系

證明id字段不是主鍵

下面2種寫(xiě)法結(jié)果一樣

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

證明id, name字段不是聯(lián)合主鍵

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

數(shù)據(jù)準(zhǔn)備

-- 建表
CREATE TABLE IF NOT EXISTS TEST01.A
(
     ID                      VARCHAR(50)               COMMENT 'ID號(hào)'       -- 01
    ,NUMS                    INT                       COMMENT '數(shù)字'       -- 02
    ,NAME                    VARCHAR(50)               COMMENT '名字'       -- 03

)
COMMENT 'A表'
STORED AS PARQUET
;

-- 插數(shù)
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');

-- 刪數(shù)
DELETE FROM TEST01.A WHERE ID = '04';
-- 刪表
DROP TABLE IF EXISTS TEST01.A;