在MySQL中统计拥有不同车辆数的用户数量的方法

要实现这个统计需求，核心是先计算每个用户的车辆拥有量，再按车辆数分组统计用户数量，同时要覆盖所有可能的车辆数（包括没有对应用户的情况），以下是具体实现方案：

1. 先统计每个用户的车辆数 #

通过关联person和car表，按用户ID分组，统计每个用户的车辆数量：

SELECT 
    p.id,
    COUNT(c.id) AS car_count
FROM person p
LEFT JOIN car c ON p.id = c.id_person
GROUP BY p.id;

执行后会得到每个用户对应的车辆数：比如LISA是3，ADAM是1，RAY是1。

2. 生成需要覆盖的车辆数范围 #

因为要像示例那样显示“2辆车”这类没有用户对应的情况，需要先生成从1到最大车辆数的连续序列，不同数据库的实现方式不同：

MySQL 8.0+ 版本（支持递归CTE） #

WITH RECURSIVE car_numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM car_numbers WHERE num < (SELECT MAX(car_count) FROM (SELECT COUNT(c.id) AS car_count FROM person p LEFT JOIN car c ON p.id = c.id_person GROUP BY p.id) t)
)
SELECT * FROM car_numbers;

这段SQL会生成从1到当前最大车辆数（示例中是3）的序列：1、2、3。

PostgreSQL 版本 #

直接用generate_series函数生成序列：

SELECT generate_series(1, (SELECT MAX(car_count) FROM (SELECT COUNT(c.id) AS car_count FROM person p LEFT JOIN car c ON p.id = c.id_person GROUP BY p.id) t)) AS num;

3. 关联得到最终统计结果 #

把生成的车辆数序列和用户车辆数统计结果左连接，再按车辆数分组统计用户数，没有对应用户的车辆数会自动填充0：

MySQL 8.0+ 完整SQL #

WITH RECURSIVE car_numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM car_numbers WHERE num < (SELECT MAX(car_count) FROM (SELECT COUNT(c.id) AS car_count FROM person p LEFT JOIN car c ON p.id = c.id_person GROUP BY p.id) t)
),
user_car_counts AS (
    SELECT 
        COUNT(c.id) AS car_count
    FROM person p
    LEFT JOIN car c ON p.id = c.id_person
    GROUP BY p.id
)
SELECT 
    cn.num AS 车辆数,
    COUNT(ucc.car_count) AS 用户数
FROM car_numbers cn
LEFT JOIN user_car_counts ucc ON cn.num = ucc.car_count
GROUP BY cn.num
ORDER BY cn.num;

PostgreSQL 完整SQL #

WITH user_car_counts AS (
    SELECT 
        COUNT(c.id) AS car_count
    FROM person p
    LEFT JOIN car c ON p.id = c.id_person
    GROUP BY p.id
)
SELECT 
    gs.num AS 车辆数,
    COUNT(ucc.car_count) AS 用户数
FROM generate_series(1, (SELECT MAX(car_count) FROM user_car_counts)) gs(num)
LEFT JOIN user_car_counts ucc ON gs.num = ucc.car_count
GROUP BY gs.num
ORDER BY gs.num;

适配低版本MySQL（无CTE支持） #

如果是MySQL 5.x这类不支持CTE的版本，可以提前创建一个数字表（比如numbers），插入1、2、3...等连续数字，再关联统计：

-- 先创建数字表
CREATE TABLE numbers (num INT);
INSERT INTO numbers VALUES (1), (2), (3); -- 插入到最大车辆数

-- 统计查询
SELECT 
    n.num AS 车辆数,
    COUNT(ucc.car_count) AS 用户数
FROM numbers n
LEFT JOIN (
    SELECT COUNT(c.id) AS car_count FROM person p LEFT JOIN car c ON p.id = c.id_person GROUP BY p.id
) ucc ON n.num = ucc.car_count
WHERE n.num <= (SELECT MAX(car_count) FROM (SELECT COUNT(c.id) AS car_count FROM person p LEFT JOIN car c ON p.id = c.id_person GROUP BY p.id) t)
GROUP BY n.num
ORDER BY n.num;

内容的提问来源于stack exchange，提问作者johnjohn22