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

冷不防 2023-06-23 08:55 1阅读 0赞

**注意：下面的案例特别重要！请重视！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 …  
![在这里插入图片描述][20191218112827176.png]  
**正确的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

**正确结果：**  
![在这里插入图片描述][20191218113235504.png]

**错误的SQL：**  
把正确SQL中最后一个on条件改为mx.maxReportDate = mn.minReportDate，注意，是把mix.reportDate改为了mx.maxReportDate。

**错误结果：**  
![在这里插入图片描述][2019121811360580.png]

**错误原因：**

1.  查询语文最高成绩时，没有查到2019-12-01的数据
2.  查询数学最低成绩时，使用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);

[20191218112827176.png]: https://img-blog.csdnimg.cn/20191218112827176.png
[20191218113235504.png]: https://img-blog.csdnimg.cn/20191218113235504.png
[2019121811360580.png]: https://img-blog.csdnimg.cn/2019121811360580.png