注意:下面的案例特别重要!请重视!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);