阿里專家教你 left join 左表合併去重技巧

2021-12-11 Java學習之道
Part1建表
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';
Part2插入數據
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);
Part3查詢異常
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;
Part4問題出現的原因
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表的記錄數。
Part5解決辦法
解決辦法都是從一個出發點出發，使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;
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;
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;
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 );
Part6實例
相信對於熟悉SQL的人來說，LEFT JOIN非常簡單，採用的時候也很多，但是有個問題還是需要注意一下。假如一個主表M有多個從表的話A B C …..的話，並且每個表都有篩選條件，那麼把篩選條件放到哪裡，就得注意嘍。
比如有個主表M，卡號是主鍵。
卡號客戶號622312345678100110016223123456781002100262231234567810031003
有個從表A，客戶號、聯繫方式是聯合主鍵，其中聯繫方式，1-座機，2-手機號碼
客戶號聯繫方式聯繫號碼10011010-78586100121881012345610021010-83743310031010-837433
如果想要查詢所有卡號對應的手機號碼兩個欄位，很簡單，SQL語句如下：
SELECT A.卡號,B.手機號碼
FROM A
LEFT JOIN B
  ON A.客戶號=B.客戶號
WHERE B.聯繫方式='2'
相信很多人這樣寫，估計實際工作中也會看到這樣的語句，並不是說這麼寫一定會錯誤，實際SQL表達的思想一定是要符合業務邏輯的。前面已經說清楚，所有卡號對應的手機號碼。所有卡號，所以首先肯定以A表作為主表，並且左關聯B表，這樣A表所有的卡號一定會顯示出來，但是如果B表的篩選條件放到最外層，這樣就相當於將A表關聯B表又做了一遍篩選，結果就是
卡號手機號碼622312345678100118810123456
就會篩選出來這麼一條數據，丟失了A表中其他的卡號。實際工作中表結構肯定沒這麼簡單，關聯的表也會很多，當有很多條件時，最好這麼寫
SELECT A.卡號,B.手機號碼
FROM A
LEFT JOIN (
    SELECT * FROM B
    B.聯繫方式='2'
    )B
  ON A.客戶號=B.客戶號
這麼寫的話，A表中的數據肯定會完全保留，又能與B表的匹配，不會丟失數據。
2表結構
表A                                    表B
3Left Join
Select * From A left join B on A.aid = B.bid;
left join是以A表的記錄為基礎的，A可以看成左表，B可以看成右表，left join是以左表為準的。換句話說，左表A的記錄將會全部表示出來，而右表B只會顯示符合搜索條件的記錄(例子中為: A.aid = B.bid)，B表記錄不足的地方均為NULL.
A表所有記錄都會顯示，A表中沒有被匹配的行（如aid=5、6的行）相應內容則為NULL。返回的記錄數一定大於A表的記錄數，如A表中aid=7行被B表匹配了3次（因為B表有三行bid=7）。
注意：在Access中A.aid、B.bid不能縮寫成aid、bid，否則會提示「不支持連結表達式」，這一點不同於Where查詢。
4Right Join
Select * From A right join B on A.aid = B.bid;
仔細觀察一下，就會發現，和left join的結果剛好相反，這次是以右表(B)為基礎的，A表不足的地方用NULL填充。
5Inner Join
Select * From A inner join B on A.aid = B.bid;
這裡只顯示出了 A.aid = B.bid的記錄.這說明inner join並不以誰為基礎，它只顯示符合條件的記錄。
inner join 等同於Where查詢如：
Select * From A， B Where A.aid = B.bid
6表的關聯修改和刪除
update A left join B on A.aid = B.bid
set A.aname = B.bname
上述SQL實際操作的表為"Select * From A left join B on A.aid = B.bid"，因此Access會提示更新13條記錄（Select查詢出的記錄就是13條）。對比「示例：2.1」返回的結果，分析update後的A表：
aid=7的記錄，被更新了3次，依次是「b1997-1」、「b1997-2」、「b1997-3」，因此其結果為最後一次更新「b1997-3」
對於上述SQL同樣可以將「A.aname = B.bname」改成「B.bname = A.aname」，執行後B表將會被修改，但是執行後B表會增加三行「0, a2005-1;0, a2005-2;0, a2006」，這也不難理解，因為Left Join執行後，B表會出現三行空值。
Where條件查詢在上面的SQL中同樣可以使用，其作用的表也是Select查詢出的關聯表。如下SQL
update A left join B on A.aid = B.bid
set A.aname = B.bname
where A.aid <> 5
執行後A表的結果：對比第一次update可以發現，aid=5的並沒有被更新。
這裡只講述left join，因為right join 和 inner join的處理過程等同於left join。另外Access中update語句中不能含有From關鍵字，這一點不同於其他資料庫。
7刪除
在Access中是不可以通過Left Join、Right Join、Inner Join來刪除某張表的記錄.
Delete From A inner join B on A.aid = B.bid
where B.bname = "b1991"
上述SQL的本意是刪除A表中aid=1的記錄，但執行後表A和表B均未發生任何變化。若想實現此目的，下述SQL可以實現
Delete From A
Where A.aid In (Select bid From B Where B.bname="b1991")
8笛卡爾積
如果A表有20條記錄，B表有30條記錄，則二者關聯後的笛卡爾積工20*30=600條記實錄。也就是說A表中的每條記錄都會於B表的所有記錄關聯一次，三種關聯方式實際上就是對「笛卡爾積」的處理方式不同。

相關焦點

  • SQL中 LEFT JOIN 左表合併去重實用技巧
    ON 條件表達式left join 是以A表為基礎，A表即左表，B表即右表。左表(A)的記錄會全部顯示，而右表(B)只會顯示符合條件表達式的記錄，如果在右表(B)中沒有符合條件的記錄，則記錄不足的地方為NULL。使用left join， A表與B表所顯示的記錄數為 1:1 或 1:0，A表的所有記錄都會顯示，B表只顯示符合條件的記錄。
  • left semi join和left join區別
  • SQL Server中的外連接(左連接(left join)右連接(right join))
    1.左連接（left join）1.解釋：將兩個表或兩個以上的表以一定的連接條件連接起來，不但返回滿足連接條件的記錄，而且會返回左表不滿足條件的記錄。2.運行原理：1.用左表的第一行分別和右表的所有行進行聯接，  如果有匹配的行，則一起輸出，如果右表有多行匹配，則結果集輸出多行，如果沒有匹配行，則結果集中只輸出一行，該輸出行左邊為左表第一行內容，右邊全部輸出null2.然後再用左表第二行和右邊所有行進行聯接，如果有匹配的行，則一起輸出，如果右表有多行匹配，則結果集輸出多行，  如果沒有匹配行，則結果集中只輸出一行，該輸出行左邊為左表第二行內容
  • SQL表連接查詢(inner join、full join、left join、right join)
    ，即內連接，把符合student.id=mark.studentid 條件的元組才選出來，也可以寫成：select s.name,m.mark from student s inner join mark m on s.id=m.studentid符合條件的只有兩條結果，查出結果為： 二、左連接-left
  • 場景分析:記錄一下使用MySQL的left join時,遇到的坑!
    但是由於對join、on、where等關鍵字的不熟悉，有時候會導致查詢結果與預期不符，所以今天我就來總結一下，一起避坑。這裡我先給出一個場景，並拋出兩個問題，如果你都能答對那這篇文章就不用看了。先給出你自己的答案，正確答案就在下面。........答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。
  • 關於Left join,你可能不知道這些......
    join 是 SQL查詢中很常見的一種操作，具體來講有join，left join, right join，full join等很多形式。具體的原理如下圖所示。但其中最常見的還是使用left join 。
  • inner join 與 left join 之間的區別
    一、sql的left join 、right join 、inner join之間的區別　　left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄 　　right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄　　inner join(等值連接) 只返回兩個表中聯結欄位相等的行舉例如下：
  • MySQL:left join 避坑指南
    先給出你自己的答案，正確答案就在下面。~~~~~~~~~~~~~~~~答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。FOR each row lt in LT {// 遍歷左表的每一行  BOOL b = FALSE;  FOR each row rt in RT such that P1(lt, rt) {// 遍歷右表每一行，找到滿足join條件的行    IF P2(lt, rt) {//滿足 where 過濾條件      t:=lt||rt;//合併行，輸出該行
  • left join,right join,inner join,full join之間的區別
    left join,right join,inner join,full join之間的區別通俗講：left以 left join 左側的表為主表
  • left join、right join和join ???
    join、 join、 right join和 inner join等等各種 join的區別。顧名思義，就是「左連接」，表1左連接表2，以左為主，表示以表1為主，關聯上表2的數據，查出來的結果顯示左邊的所有數據，然後右邊顯示的是和左邊有交集部分的數據。
  • MySql 之 left join 避坑指南
    先給出你自己的答案，正確答案就在下面。........答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。FOR each row lt in LT {// 遍歷左表的每一行 BOOL b = FALSE; FOR each row rt in RT such that P1(lt, rt) {// 遍歷右表每一行，找到滿足join條件的行 IF P2(lt, rt) {//滿足 where 過濾條件 t:=lt||rt;//合併行，輸出該行
  • 關於Left join,避坑指南
    join 是 SQL查詢中很常見的一種操作，具體來講有join，left join, right join，full join等很多形式。具體的原理如下圖所示。但其中最常見的還是使用left join 。
  • inner join 與 left join 之間的區別,你真的搞懂了嗎?
    與 left join 之間的區別，以前以為自己搞懂了，今天從前端取參數的時候發現不是預想中的結果，才知道問題出在inner join 上了。一、sql的left join 、right join 、inner join之間的區別left join(左聯接) ：返回包括左表中的所有記錄和右表中聯結欄位相等的記錄right join(右聯接) ：返回包括右表中的所有記錄和左表中聯結欄位相等的記錄inner join(等值連接) ：只返回兩個表中聯結欄位相等的行舉例如下：
  • 【面試必備】SQL中left join、right join、inner join的區別
    20060324023　　　　　20060324034　　　　　20060324048　　　　　20060324081.left joinsql語句如下:select * from Aleft join B on A.aID = B.bID結果如下:aID　　　　　aNum
  • MySQL:Left Join 避坑指南
    但是由於對join、on、where等關鍵字的不熟悉，有時候會導致查詢結果與預期不符，所以今天我就來總結一下，一起避坑。這裡我先給出一個場景，並拋出兩個問題，如果你都能答對那這篇文章就不用看了。先給出你自己的答案，正確答案就在下面。答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。
  • 你知道 Sql 中 left join 的底層原理嗎?
    ，就是保留左表的全部信息，然後把右表往左表上拼接，如果拼不上就是 null。除了 left join以外，還有inner join、outer join、right join，這些不同的 join 能達到的什麼樣的效果，大家應該都了解了，如果不了解的可以看看網上的帖子或者隨便一本 Sql 書都有講的。今天我們不講這些 join 能達到什麼效果，我們主要講這些 join 的底層原理是怎麼實現的，也就是具體的效果是怎麼呈現出來的。為什麼要講底層原理呢？
  • SQL語言中,inner join 、left join 、right join和full join的定義和圖示
    left join 是左連接，返回坐表中所有的行以及右表中符合條件的行。right join右連接，是返回右表中所有的行及左表中符合條件的行。full join全連接，是返回左表中所有的行及右表中所有的行，並按條件連接。
  • left join、right join和join,傻傻分不清?
    join、 join、 right join和 inner join等等各種 join的區別。顧名思義，就是「左連接」，表1左連接表2，以左為主，表示以表1為主，關聯上表2的數據，查出來的結果顯示左邊的所有數據，然後右邊顯示的是和左邊有交集部分的數據。
  • MySQL:LEFT JOIN 避坑指南
    但是由於對join、on、where等關鍵字的不熟悉，有時候會導致查詢結果與預期不符，所以今天我就來總結一下，一起避坑。這裡我先給出一個場景，並拋出兩個問題，如果你都能答對那這篇文章就不用看了。先給出你自己的答案，正確答案就在下面。~~~~~~~~~~~~~~~~答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。
  • SQL join語句總結
    全外連接可以理解為left join與right join的併集。對應Venn圖求並。由於MySQL中沒有提供full outer join關鍵字，我們需要用left join 與right join 再結合 union來模擬full outer join，用union當然是因為union會去重。