今天在写一个sql的时候,碰到一个知识盲区,也是我对 left join 理解的错误。
一、表结构
学生表
CREATE TABLE stu (
`id` varchar(50) NOT NULL COMMENT '主键ID',
`name` varchar(50) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
课程表
CREATE TABLE `course` (
`id` varchar(50) NOT NULL COMMENT '主键ID',
`name` varchar(50) NOT NULL COMMENT '课程名字',
`stu_id` varchar(50) NOT NULL COMMENT '学生id',
`score` INT NOT NULL COMMENT '得分',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
插入数据
二、需求
我们要查询出每个人大于60分有多少门课程(一门没有的显示0)
2-1、错误的思考
啪一下、很快啊,我就写出了
一个左连接、一个条件判断,我全写好了。
SELECT
stu.`name`,
COUNT(course.id) num
FROM stu
LEFT JOIN course ON stu.id = course.stu_id
WHERE course.score > 60
GROUP BY stu.id
但是这个查询出来的结果如下,明显少了我们的学渣李四
name | num |
---|---|
张三 | 3 |
王五 | 1 |
2-2、正确的思考
1、我们知道左连接的查询结果应当是下面带颜色的部分
2、当我们使用连接查询的时候,那就是把两张表组合成了一张,这时候WHERE的条件当然是对这张组合表起作用了。(所以我们上面的WHERE course.score > 60 就直接去掉了李四)
因为我们的分数大于60,只对分数表有作用,所以我们应该在组合之前去WHERE
SELECT
stu.`name`,
COUNT(course.id) num
FROM stu
LEFT JOIN course ON stu.id = course.stu_id AND course.score > 60
GROUP BY stu.id