MySQL中多个left join on关联条件的顺序说明
作者:p7+ 发布时间:2024-01-15 14:01:46
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。
错误结果:
错误原因:
查询语文最高成绩时,没有查到2019-12-01的数据
查询数学最低成绩时,使用on与语文最高成绩关联,因为没有查到语文最高成绩的日期,所以两个表关联时,数学最低成绩即使有数据,也会因为语文最高成绩无数据而被忽略。
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 初学者很容易犯的错误,大家稍微注意点。
来源:https://blog.csdn.net/qq_30038111/article/details/103594735
猜你喜欢
- 这个收藏本站、设为首页代码相信每个网站都会用到,这么常用的代码,网络上流行的一般是很多年前的代码版本,只有兼容IE,对其它浏览器没有考虑,下
- 工欲善其事,必先利其器。作为更专业的前端工程师,我们需要强劲的IDE协助我们写出规范、美观、漂亮的JavaScript代码,首先要作的就是对
- 01-初心缘由最近在研究语音识别方向,看了很多的语音识别的资料和文章,了解了一下语音识别的前世今生,其中包含了很多算法的演变,目前来说最流行
- created页面加载未渲染html之前执行。mounted渲染html后再执行。由于created在html模板生产之前所以无法对Dom进
- openCV是基于C++开发的一个强大的图像处理库。在用C++处理图像或视频时通常会使用到openCV这个库,但是这个库并非C++中的标准库
- fileno()方法返回所使用的底层实现,要求从操作系统I/O操作的整数文件描述符。语法以下是fileno()方法的语法:fil
- 前言v-model 可以在组件上使用以实现双向绑定。首先让我们回忆一下 v-model 在原生元素上的用法:<input v-mode
- 协同过滤在 用户 —— 物品(user - item)的数据关系下很容易收集到一些偏好信息(preference),比如评分。利用这些分散的
- 本文实例讲述了MySQL学习笔记之创建、删除、修改表的方法。分享给大家供大家参考,具体如下:创建表:create table users(
- 一、了解seleniumSelenium是一个用于测试网站的自动化测试工具,支持各种浏览器包括Chrome、Firefox、Safar等浏览
- 相信不少人,写代码忘我的时候,都会忘记层级之间的缩进,导致代码,看着非常不清晰,这个时候,你是否还在手动一点点缩进,这个时候,我们需要利用编
- 相信各位网页爱好者都对网页的loading很感兴趣吧!,想不想知道如何做一个:)现在给大家介绍一下一种loading的制作..效果图:loa
- 1.cookie的作用cookie 是指某些网站为了辨别用户身份、进行session跟踪而储存在用户本地终端上的数据,就像有些网站上的一些数
- 本文介绍使用aspjpeg组件实现图片的半透明描边的效果,描边效果演示:参数说明'big 原图路径(相对)'small 生成
- 有2个不同的方法增加用户:通过使用GRANT语句或通过直接操作MySQL授权表。比较好的方法是使用GRANT语句,因为他们是更简明并且好像错
- python 遍历字符串(含汉字)实例详解s = "中国china"for j in s: print j首
- 背景在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个va
- 1. 在 Python 中 XML 文件的编码问题1.Python 使用的xml.etree.ElementTree库只支持解析和生成标准的
- 本文实例讲述了Python通过调用mysql存储过程实现更新数据功能。分享给大家供大家参考,具体如下:一、需求分析由于管理费率配置错误,生成
- 1、两个函数介绍总得来说,connectedComponents()仅仅创建了一个标记图(图中不同连通域使用不同的标记,和原图宽高一致),c