分享：SQL中 LEFT JOIN 左表合併去重實用技巧

Luson發表於2021-10-20
SQL

建表：

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` varchar(200) DEFAULT NULL,
  `sponsor_id` varchar(20) DEFAULT NULL COMMENT '業務發起人',
  `gmt_create_user` int(11) NOT NULL COMMENT '建立人id',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `gmt_modified` datetime DEFAULT NULL COMMENT '修改時間',
  `gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='測試表1';
CREATE TABLE `table2` (
  `kid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `sponsor_id` varchar(20) DEFAULT NULL COMMENT '業務發起人',
  `type` int(11) NOT NULL COMMENT '建立人id',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `gmt_modified` datetime DEFAULT NULL COMMENT '修改時間',
  `gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
  PRIMARY KEY (`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='測試表2';

插入資料：

`INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '11', '10', 1, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '12', '10', 2, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '13', '10', 3, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '14', '20', 4, '2018-10-10 20:34:03', NULL, NULL);

圖片

`INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (5, 't5', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (6, 't6', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (7, 't7', '10', 2, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (8, 't1', '11', 1, '2018-10-10 20:38:10', NULL, NULL);

圖片

查詢異常：

`SELECT
 a.*,
 b.type 
FROM
 table1 a
 LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
WHERE
 b.type = 1 
 AND a.sponsor_id = 10;

圖片

簡單說明問題出現的原因：

MySQL left join 語句格式為：A LEFT JOIN B ON 條件表示式

left join 是以A表為基礎，A表即左表，B表即右表。

左表(A)的記錄會全部顯示，而右表(B)只會顯示符合條件表示式的記錄，如果在右表(B)中沒有符合條件的記錄，則記錄不足的地方為NULL。

使用left join， A表與B表所顯示的記錄數為 1:1 或 1:0，A表的所有記錄都會顯示，B表只顯示符合條件的記錄。

但如果B表符合條件的記錄數大於1條，就會出現1:n的情況，這樣left join後的結果，記錄數會多於A表的記錄數。

所以解決辦法 都是從一個出發點出發，使A表與B表所顯示的記錄數為 1:1對應關係。

解決方法：

使用非唯一標識的欄位做關聯

  1. DISTINCT

    select DISTINCT(id) from a left join b on a.id=b.aid

    DISTINCT查詢結果是 第一個表唯一的資料 重複的結果沒顯示出來

    SELECT
DISTINCT(a.id), a.*,
b.type 
FROM
table1 a
LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
WHERE
b.type = 1 
AND a.sponsor_id = 10;
    SELECT
DISTINCT a.*,
b.type 
FROM
table1 a
LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
WHERE
b.type = 1 
AND a.sponsor_id = 10;

  2. GROUP BY

    select * from a left join(select id from b group by id) as b on a.id=b.aid

    拿出b表的一條資料關聯 使A表與B表所顯示的記錄數為 1:1對應關係。

    SELECT 
a.*,
b.type 
FROM
table1 a
LEFT JOIN ( SELECT * FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
WHERE
b.type = 1 
AND a.sponsor_id = 10;

  3. max取唯一

    select * from a left join (select max(id) from table group by id) as b on a.id=b.aid

    拿出b表的最後一條資料關聯

    SELECT
a.*,
b.type 
FROM
table1 a
LEFT JOIN ( SELECT MAX( kid ), type, sponsor_id FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
WHERE
b.type = 1 
AND a.sponsor_id = 10;

  4. IN巧用

    SELECT
a.* 
FROM
table1 a 
WHERE
a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );
    SELECT
a.*,
1 
FROM
table1 a 
WHERE
a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );

    圖片

相信對於熟悉SQL的人來說，LEFT JOIN非常簡單，採用的時候也很多，但是有個問題還是需要注意一下。假如一個主表M有多個從表的話A B C …..的話，並且每個表都有篩選條件，那麼把篩選條件放到哪裡，就得注意嘍。

比如有個主表M，卡號是主鍵。

圖片

有個從表A，客戶號、聯絡方式是聯合主鍵，其中聯絡方式，1-座機，2-手機號碼

圖片

如果想要查詢所有卡號對應的手機號碼兩個欄位，很簡單，SQL語句如下：

SELECT A.卡號,B.手機號碼
FROM A
LEFT JOIN B
  ON A.客戶號=B.客戶號
WHERE B.聯絡方式='2'

相信很多人這樣寫，估計實際工作中也會看到這樣的語句，並不是說這麼寫一定會錯誤，實際SQL表達的思想一定是要符合業務邏輯的。

前面已經說清楚，所有卡號對應的手機號碼。所有卡號，所以首先肯定以A表作為主表，並且左關聯B表，這樣A表所有的卡號一定會顯示出來，但是如果B表的篩選條件放到最外層，這樣就相當於將A表關聯B表又做了一遍篩選，結果就是

圖片

就會篩選出來這麼一條資料，丟失了A表中其他的卡號。

實際工作中表結構肯定沒這麼簡單，關聯的表也會很多，當有很多條件時，最好這麼寫

SELECT A.卡號,B.手機號碼
FROM A
LEFT JOIN (
    SELECT * FROM B
    B.聯絡方式='2'
    )B
  ON A.客戶號=B.客戶號

這麼寫的話，A表中的資料肯定會完全保留，又能與B表的匹配，不會丟失資料。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章