SQL多表多欄位比對方法範例程式碼

2022-05-18 16:01:24

表-表比較

整體思路

兩張表條數一樣
- 條數相同是前提，然後比較欄位值才有意義
兩表欄位值完全相同【兩表所有欄位的值相同】
- 兩表所有欄位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;