如何基于MySQL中第一张表指定列值统计两张表记录数

嘿，我来帮你搞定这个MySQL统计需求～ 根据你给出的两张表结构，我们可以通过关联查询结合分组统计的方式，轻松实现基于register表特定列取值的双表记录数统计。下面分两种常见场景给你具体方案：

场景1：按register表的cityId分组统计 #

如果你的需求是按城市维度，统计每个城市对应的register记录数，以及该城市下所有员工关联的guest_list总记录数，可以用以下SQL：

SELECT
    r.cityId,
    COUNT(r.id) AS register_count, -- 统计当前cityId的register记录数
    COUNT(gl.id) AS guest_count    -- 统计当前cityId关联的guest_list记录数
FROM
    register r
LEFT JOIN
    guest_list gl ON r.empSignupId = gl.empSignupId
GROUP BY
    r.cityId;

语句说明 #

• 使用LEFT JOIN可以保证所有register的记录都会被统计，即使某个城市的员工没有对应的访客记录。
• COUNT(r.id)会统计每个分组下register表的有效记录数（不会忽略NULL，因为r.id是主键不可能为NULL）。
• COUNT(gl.id)只会统计关联到的guest_list记录数，没有关联时会返回0。

基于你提供的示例数据，执行结果如下： #

+--------+---------------+-------------+
| cityId | register_count | guest_count |
+--------+---------------+-------------+
| 1 | 4 | 4 |
| 2 | 2 | 0 |
+--------+---------------+-------------+

场景2：按register表的empSignupId分组统计 #

如果你的需求是按员工注册项维度，统计每个empSignupId对应的register记录数，以及该员工关联的访客数量，可以用以下SQL：

SELECT
    r.empSignupId,
    COUNT(r.id) AS register_count, -- 统计当前empSignupId的register记录数
    COUNT(gl.id) AS guest_count    -- 统计当前empSignupId关联的guest_list记录数
FROM
    register r
LEFT JOIN
    guest_list gl ON r.empSignupId = gl.empSignupId
GROUP BY
    r.empSignupId;

基于示例数据的执行结果： #

+-------------+---------------+-------------+
| empSignupId | register_count | guest_count |
+-------------+---------------+-------------+
| 4 | 1 | 0 |
| 3 | 1 | 2 |
| 11 | 1 | 2 |
| 20 | 1 | 0 |
| 21 | 1 | 0 |
| 22 | 1 | 0 |
+-------------+---------------+-------------+

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