MySQL多個(gè)left join on關(guān)聯(lián)條件順序

注意：下面的案例特別重要！請(qǐng)重視！SQL有點(diǎn)長(zhǎng)，但確實(shí)是干貨！

結(jié)論

如果存在多個(gè)left join on，請(qǐng)注意on后面的條件與哪個(gè)表關(guān)聯(lián)。這一條統(tǒng)計(jì)的SQL很重要！例如表A，B，C，A left join B on A.x = B.x left join C on A.x = C.x，B和C的都要和A建立關(guān)聯(lián)，B和C之間是沒(méi)有任何數(shù)據(jù)上的關(guān)系。

但是 如果把A.x = C.x改成B.x = C.x，那么B和C的表數(shù)據(jù)先建立關(guān)聯(lián)并過(guò)濾數(shù)據(jù)，再與A表數(shù)據(jù)進(jìn)行關(guān)聯(lián)，這樣可能會(huì)出現(xiàn)數(shù)據(jù)丟失！

案例

有一張分?jǐn)?shù)表，表字段有日期、姓名、語(yǔ)文得分和數(shù)學(xué)得分等，請(qǐng)統(tǒng)計(jì)每個(gè)日期中，語(yǔ)文最高得分的姓名和分?jǐn)?shù)，數(shù)學(xué)最低得分的姓名和分?jǐn)?shù)。

思路：過(guò)濾出所有日期 left join 篩選語(yǔ)文 on … left join 數(shù)學(xué)得分 on …

正確的SQL：

SELECT
	* 
FROM
	( SELECT report_date reportDate FROM tb_more_left_join mlj GROUP BY mlj.report_date ) mix
	LEFT JOIN (
SELECT
	mlj.report_date maxReportDate,
	GROUP_CONCAT( mlj.user_name ) maxUserNames,
	a.maxScore 
FROM
	tb_more_left_join mlj
	LEFT JOIN ( SELECT report_date, MAX( chinese_score ) maxScore FROM tb_more_left_join mlj GROUP BY mlj.report_date ORDER BY NULL ) a ON mlj.report_date = a.report_date 
WHERE
	mlj.chinese_score = a.maxScore 
GROUP BY
	mlj.report_date 
	) mx ON mix.reportDate = mx.maxReportDate
	LEFT JOIN (
SELECT
	mlj.report_date minReportDate,
	GROUP_CONCAT( mlj.user_name ) minUserNames,
	a.minScore 
FROM
	tb_more_left_join mlj
	LEFT JOIN ( SELECT report_date, MIN( math_score ) minScore FROM tb_more_left_join mlj GROUP BY mlj.report_date ORDER BY NULL ) a ON mlj.report_date = a.report_date 
WHERE
	mlj.math_score = a.minScore 
GROUP BY
	mlj.report_date 
	) mn ON mix.reportDate = mn.minReportDate

正確結(jié)果：

錯(cuò)誤的SQL：

把正確SQL中最后一個(gè)on條件改為mx.maxReportDate = mn.minReportDate，注意，是把mix.reportDate改為了mx.maxReportDate。

錯(cuò)誤結(jié)果：

錯(cuò)誤原因：

• 查詢(xún)語(yǔ)文最高成績(jī)時(shí)，沒(méi)有查到2019-12-01的數(shù)據(jù)
• 查詢(xún)數(shù)學(xué)最低成績(jī)時(shí)，使用on與語(yǔ)文最高成績(jī)關(guān)聯(lián)，因?yàn)闆](méi)有查到語(yǔ)文最高成績(jī)的日期，所以?xún)蓚€(gè)表關(guān)聯(lián)時(shí)，數(shù)學(xué)最低成績(jī)即使有數(shù)據(jù)，也會(huì)因?yàn)檎Z(yǔ)文最高成績(jī)無(wú)數(shù)據(jù)而被忽略。

SQL數(shù)據(jù)

CREATE TABLE `tb_more_left_join`  (
  `id` int(11) NOT NULL,
  `report_date` date NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `math_score` int(255) NULL DEFAULT NULL,
  `chinese_score` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_more_left_join` VALUES (1, '2019-12-01', '盲僧', 70, NULL);
INSERT INTO `tb_more_left_join` VALUES (2, '2019-12-01', '薇恩', 100, NULL);
INSERT INTO `tb_more_left_join` VALUES (3, '2019-12-02', '趙信', 30, 60);
INSERT INTO `tb_more_left_join` VALUES (4, '2019-12-02', '琴女', NULL, 100);
INSERT INTO `tb_more_left_join` VALUES (5, '2019-12-03', '蠻王', 50, 100);
INSERT INTO `tb_more_left_join` VALUES (6, '2019-12-03', '艾希', 100, 100);
INSERT INTO `tb_more_left_join` VALUES (7, '2019-12-03', '亞索', 60, 90);

使用left join的on后查詢(xún)碰到的大坑

很多時(shí)候我們?cè)谑褂?nbsp; LEFT JOIN   ...... ON .... 時(shí)， 除了連接兩個(gè)表的字段條件外，我們往往還需要一些等值或者范圍 等等類(lèi)似的數(shù)據(jù)篩選條件。

那么對(duì)于初學(xué)者，往往會(huì)犯一個(gè)錯(cuò)誤，就是 想當(dāng)然 地 認(rèn)為， ON 后面的條件是逐一執(zhí)行的，因?yàn)闆](méi)有了解清楚 ON 后面接條件的規(guī)則。

是個(gè)什么樣的場(chǎng)景？ 

看實(shí)例講解：

userinfo 表 ：

（找兼職的人員名單信息表）

jobinfo表 ：

（兼職工作信息及職業(yè)要求表）

業(yè)務(wù)需求：

根據(jù)職業(yè)要求 給 找兼職的人員 匹配上 目前 可以做的兼職，輸出數(shù)據(jù)條。 

例如，李三是一個(gè)程序員，他迫于經(jīng)濟(jì)壓力，不得不向社會(huì)低頭，想找一些自己能做的兼職。

使用 WHERE

如果我們不用  left join ...... on ... , 僅僅使用 where，那么簡(jiǎn)單寫(xiě)下sql是：

SELECT * 
FROM userinfo AS u ,jobinfo AS j 
WHERE u.userProfession=j.professionRequire
AND j.professionRequire='程序員'

查詢(xún)出來(lái)的結(jié)果如下：

是我們需要的結(jié)果，可以看的，程序員李三能做的兼職有，送外賣(mài)或者當(dāng)保安。 

使用 LEFT JOIN ...... ON  ......

初學(xué)者（罪過(guò)）寫(xiě)的SQL ：

想當(dāng)然地把篩選條件 職業(yè)要求為 ‘ 程序員’  直接 拼接在 ON 后面

SELECT * 
FROM userinfo AS u 
LEFT JOIN   
jobinfo AS j 
ON u.userProfession=j.professionRequire
AND j.professionRequire='程序員'

這樣地拼接篩選條件其實(shí)是達(dá)不到所想要的效果的，先來(lái)看看這樣的執(zhí)行結(jié)果：

可以看到查詢(xún)出來(lái)很多我們不想要的數(shù)據(jù)，為什么會(huì)這樣？

原因 

因?yàn)槿绻苯影殃P(guān)聯(lián)表的篩選條件拼接在 ON 后， 執(zhí)行的順序其實(shí)是：

將 jobinfo 表 按照篩選條件  professionRequire='程序員'  執(zhí)行后作為子查詢(xún)，再執(zhí)行 LEFT JOIN ...... ON 。

也就是第一步變成了執(zhí)行  SELECT *  FROM jobinfo AS j  WHERE  j.professionRequire='程序員'  

然后再進(jìn)行連接查詢(xún)，也就是 

整個(gè)sql語(yǔ)句其實(shí)變成了：

SELECT * 
FROM userinfo AS u 
LEFT JOIN   
(SELECT *  FROM jobinfo  WHERE jobinfo.professionRequire='程序員') AS j 
ON 
u.userProfession=j.professionRequire

這樣查詢(xún)出來(lái)，顯然不是我們想要的結(jié)果。

那么我們?cè)谑褂?nbsp;LEFT JOIN ...... ON  ...... 拼接篩選條件時(shí)，我們應(yīng)該怎么做？

配合 WHERE 使用：

SELECT * 
FROM userinfo AS u 
LEFT JOIN   jobinfo AS j 
ON u.userProfession=j.professionRequire
WHERE j.professionRequire='程序員'

結(jié)果：

我們把篩選條件配合where去使用， 執(zhí)行的邏輯就是：

先執(zhí)行LEFT JOIN ...... ON  ......  先將關(guān)聯(lián)兩個(gè)表之后的數(shù)據(jù)查詢(xún)出來(lái)；

再按照 professionRequire='程序員'   條件，進(jìn)行數(shù)據(jù)篩選。

所以這是我們想要得到的結(jié)果。

這是一個(gè)使用 LEFT JOIN 的 ON  初學(xué)者很容易犯的錯(cuò)誤，大家稍微注意點(diǎn)。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論