MySQL 多表关联统计，无数据的记录正常显示并补0

统计course 表中各课程所对应的课次（lesson表）和学生（student表）数量

以下3个表中，student 和 lesson 表中的course_id 字段，都关联至course 表中的id字段
course表

lesson表

student表

SELECt
    c.id cId,c.`name` cName,count( DISTINCT s.id ) stuNum,count( DISTINCT l.id ) lessonNum 
FROM
    student s
    JOIN course c ON s.course_id = c.id
    JOIN lesson l ON l.course_id = c.id 
GROUP BY
    c.id;

course 表有3条记录，对应3种课程，但是此处的统计结果中没有 course.name=Java 的记录。
原因在于Java 课程没有对应的student和lesson，所以在联表的时候，没有将该课程记录进行统计。
预期结果应该是列出全部课程，但是像此处的Java 课程没有对应的student和lesson，那么就将统计数量记为0 。

最外层查询course 表，这样可以确保course 表中的记录都在。然后Left Join一个子查询，这个子查询其实就是前面那条查询sql。
子查询的结果依然只有VB和Python两条，但因为整个子查询的结果集Left Join到course表，所以统计结果中course表中的记录是完整的。

SELECt
    c.id cId,c.`name` cName,IFNULL( d.stuCount, 0 ) stuNum,IFNULL( d.lessonCount, 0 ) lessonNum 
FROM
    course c
    LEFT JOIN (
    SELECt
        c.id cId,c.`name` cName,count( DISTINCT s.id ) stuCount,count( DISTINCT l.id ) lessonCount 
    FROM
        course c
        JOIN student s ON s.course_id = c.id
        JOIN lesson l ON l.course_id = c.id 
    GROUP BY
        c.id 
    ) d ON d.cId = c.id;

符合预期的结果，但依然可以优化。

前面写的SQL2，用左联的方式，确保左表中的记录完整，那就直接左联到底。
在SQL1的基础上，调整下3个表的关联顺序和关联方式。
course表在最左，后面的student、lesson表依次左联，也可以有同样的效果，这样就可以把子查询给省掉了。

SQL3

SELECt
    c.id cId,c.`name` cName,count( DISTINCT s.id ) stuNum,count( DISTINCT l.id ) lessonNum 
FROM
    course c
    LEFT JOIN student s ON s.course_id = c.id
    LEFT JOIN lesson l ON l.course_id = c.id 
GROUP BY
    c.id;

查询结果

cId	cName	sNum	lessNum
1	VB	2	2
2	Python	1	2
3	Java	0	0

结果符合预期。

后续SQL对比

分别给SQL2和SQL3的sql语句前加上explain，就可以查看sql执行的情况

EXPLAIN SELECT cId,cName...... 

SQL2

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	c		ALL					3	100
1	PRIMARY		ref			4	c.id	2	100
2	DERIVED	s		index	FKdfypyqtj9kcxm99s	FKdfypyqtj9kcxm99s	5		3	100	Using where; Using index; Using temporary; Using filesort
2	DERIVED	c		eq_ref	PRIMARY	PRIMARY	4	course_id	1	100
2	DERIVED	l		ref	FKjs3c7sk5lc7s807	FKjs3c7sk5lc7s807	5	course_id	2	100	Using index

SQL3

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	c		index	PRIMARY	PRIMARY	4		3	100
1	SIMPLE	s		ref	FKdfypyqtj9kcxm99s	FKdfypyqtj9kcxm99s	5	c.id	1	100	Using index
1	SIMPLE	l		ref	FKjs3c7sk5lc7s807	FKjs3c7sk5lc7s807	5	c.id	2	100	Using index

分析

很容易发现，SQL2查询步骤更多，而且各种查询的类型（type字段值）都快凑齐了，尤其是第一个为全表扫描，虽然扫描的是course表，里面数据不会特别多，影响不算大，但如果在其他场景就不一定适用了。
再看Extra里面，SQL2的查询中除了正常索引外，还生成了临时表（Using temporary）和文件排序（Using filesort），这俩也会增加查询的耗时，所以一般可以的话也得把它俩优化掉。
对比下来，SQL3没啥毛病，查询都用上了索引，type都是ref或index级别的，也还行。

结论

在文中场景下，SQL3是比较推荐的写法。
可能还有更高效、优雅的实现方式，欢迎大家一起交流讨论