亲宝软件园·资讯

展开

MySQL中多个left join on关联条件的顺序说明

p7+ 人气:0

MySQL多个left join on关联条件顺序

注意:下面的案例特别重要!请重视!SQL有点长,但确实是干货!

结论

如果存在多个left join on,请注意on后面的条件与哪个表关联。这一条统计的SQL很重要!例如表A,B,C,A left join B on A.x = B.x left join C on A.x = C.x,B和C的都要和A建立关联,B和C之间是没有任何数据上的关系。

但是 如果把A.x = C.x改成B.x = C.x,那么B和C的表数据先建立关联并过滤数据,再与A表数据进行关联,这样可能会出现数据丢失!

案例

有一张分数表,表字段有日期、姓名、语文得分和数学得分等,请统计每个日期中,语文最高得分的姓名和分数,数学最低得分的姓名和分数。

思路:过滤出所有日期 left join 筛选语文 on … left join 数学得分 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

正确结果:

错误的SQL:

把正确SQL中最后一个on条件改为mx.maxReportDate = mn.minReportDate,注意,是把mix.reportDate改为了mx.maxReportDate。

错误结果:

错误原因:

SQL数据

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后查询碰到的大坑

很多时候我们在使用  LEFT JOIN   ...... ON .... 时, 除了连接两个表的字段条件外,我们往往还需要一些等值或者范围 等等类似的数据筛选条件。

那么对于初学者,往往会犯一个错误,就是 想当然 地 认为, ON 后面的条件是逐一执行的,因为没有了解清楚 ON 后面接条件的规则。

是个什么样的场景? 

看实例讲解:

userinfo 表 :

(找兼职的人员名单信息表)

jobinfo表 :

(兼职工作信息及职业要求表)

业务需求:

根据职业要求 给 找兼职的人员 匹配上 目前 可以做的兼职,输出数据条。 

例如,李三是一个程序员,他迫于经济压力,不得不向社会低头,想找一些自己能做的兼职。

使用 WHERE

如果我们不用  left join ...... on ... , 仅仅使用 where,那么简单写下sql是:

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

查询出来的结果如下:

是我们需要的结果,可以看的,程序员李三能做的兼职有,送外卖或者当保安。 

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

初学者(罪过)写的SQL :

想当然地把筛选条件 职业要求为 ‘ 程序员’  直接 拼接在 ON 后面

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

这样地拼接筛选条件其实是达不到所想要的效果的,先来看看这样的执行结果:

可以看到查询出来很多我们不想要的数据,为什么会这样?

原因 

因为如果直接把关联表的筛选条件拼接在 ON 后, 执行的顺序其实是:

将 jobinfo 表 按照筛选条件  professionRequire='程序员'  执行后作为子查询,再执行 LEFT JOIN ...... ON 。

也就是第一步变成了执行  SELECT *  FROM jobinfo AS j  WHERE  j.professionRequire='程序员'  

然后再进行连接查询,也就是 

整个sql语句其实变成了:

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

这样查询出来,显然不是我们想要的结果。

那么我们在使用 LEFT JOIN ...... ON  ...... 拼接筛选条件时,我们应该怎么做?

配合 WHERE 使用:

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

结果:

我们把筛选条件配合where去使用, 执行的逻辑就是:

先执行LEFT JOIN ...... ON  ......  先将关联两个表之后的数据查询出来;

再按照 professionRequire='程序员'   条件,进行数据筛选。

所以这是我们想要得到的结果。

这是一个使用 LEFT JOIN 的 ON  初学者很容易犯的错误,大家稍微注意点。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

加载全部内容

相关教程
猜你喜欢
用户评论