SQL查询：统计表1中customer_id在表2中的重复次数

假设两张表分别为table1和table2（可替换为你的实际表名），以下是两种常用的实现方式：

方法1：左连接+分组统计 #

这是性能较优的写法，尤其适合数据量较大的场景：

SELECT 
    t1.customer_id,
    COUNT(t2.customer_id) AS occurrence_count
FROM table1 t1
LEFT JOIN table2 t2 ON t1.customer_id = t2.customer_id
GROUP BY t1.customer_id;

• LEFT JOIN保证表1的所有customer_id都会被保留，哪怕在表2中没有匹配项
• COUNT(t2.customer_id)会自动忽略NULL值，所以表2中无匹配的customer_id会显示0
• GROUP BY t1.customer_id按表1的客户ID分组，统计每组在表2中的出现次数

方法2：子查询方式 #

逻辑更直观，适合快速理解需求：

SELECT 
    customer_id,
    (SELECT COUNT(*) FROM table2 t2 WHERE t2.customer_id = t1.customer_id) AS occurrence_count
FROM table1 t1;

对表1的每一行客户ID，通过子查询直接统计表2中对应ID的出现次数。

额外处理：表1存在重复客户ID的情况 #

如果表1本身有重复的customer_id，且你需要统计去重后的每个客户ID在表2中的次数，可修改为：

-- 基于左连接的写法
SELECT 
    DISTINCT t1.customer_id,
    COUNT(t2.customer_id) AS occurrence_count
FROM table1 t1
LEFT JOIN table2 t2 ON t1.customer_id = t2.customer_id
GROUP BY t1.customer_id;

-- 基于子查询的写法
SELECT 
    customer_id,
    (SELECT COUNT(*) FROM table2 t2 WHERE t2.customer_id = t1.customer_id) AS occurrence_count
FROM (SELECT DISTINCT customer_id FROM table1) t1;

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