关联3张表时如何统计包含0计数的类型与颜色组合？

数据表结构与数据 #

Vehicle表 #

id  brand   type_id color_id
1   Toyota     1       2
2   Toyota     2       3
3   GMC        2       1
4   BMW        2       1

Type表 #

id  name
1   Truck
2   Car

Color表 #

id  name
1   White
2   Red
3   Black

预期结果 #

需要统计类型与颜色的所有组合的车辆数量，包含计数为0的情况，预期输出如下：

type.name   color.name          count
Truck         White               0
Truck          Red                1
Truck         Black               0
Car           White               2
Car            Red                0
Car           Black               1

尝试的SQL语句及问题 #

用户尝试的SQL语句如下：

SELECT type.name, color.name, count(vehicle.id) count
FROM vehicle
RIGHT JOIN type on vehicle.type_id = type.id
RIGHT JOIN color on color.id = vehicle.color_id
GROUP BY type.name, color.name

实际结果仅返回有车辆的组合，缺少计数为0的记录：

type.name   color.name          count
Truck          Red                1
Car           White               2
Car           Black               1

解决方案 #

问题出在原语句的连接逻辑：从vehicle表出发做两次右连接，无法生成Type和Color的所有笛卡尔积组合。正确的思路是先生成所有类型与颜色的组合，再关联车辆表统计数量，具体SQL如下：

SELECT 
    t.name AS `type.name`,
    c.name AS `color.name`,
    COUNT(v.id) AS count
FROM Type t
CROSS JOIN Color c
LEFT JOIN Vehicle v 
    ON v.type_id = t.id AND v.color_id = c.id
GROUP BY t.name, c.name
ORDER BY t.name, c.name;

逻辑说明： #

1. Type t CROSS JOIN Color c：生成类型与颜色的所有可能组合（共2×3=6种）
2. LEFT JOIN Vehicle v：将所有组合与车辆表关联，没有匹配车辆的组合会保留NULL值
3. COUNT(v.id)：统计每个组合对应的车辆数，NULL值会被COUNT忽略，自然得到0
4. GROUP BY t.name, c.name：按类型和颜色分组统计
5. ORDER BY：保证结果顺序与预期一致

执行该语句后，即可得到包含所有组合及计数为0情况的预期结果。

内容的提问来源于stack exchange，提问作者Plateau The Small