如何用SQL统计存在共同字段/值组合的关联记录数

这个需求完全可以通过SQL实现，核心是解决关联关系的传递性问题（即A关联B、B关联C时，A/B/C同属一个关联组），需要用到支持递归公用表表达式（CTE）的数据库版本（MySQL 8.0+、PostgreSQL 8.4+、SQL Server 2008+等均支持）。

实现逻辑 #

• 先提取所有直接关联的记录对：两条记录只要有相同的phone/email值，就标记为直接关联
• 通过递归遍历所有关联路径，找出所有互相连通的记录，划分到同一个关联组
• 统计每个关联组的总记录数，匹配回对应的RecordID输出结果

对应SQL语句 #

假设存储属性的表名为record_contact，使用时请替换为你实际的表名，语句如下：

WITH RECURSIVE
-- 第一步：找出所有直接关联的记录ID对，同时补充每个记录自身的关联（避免孤立记录丢失）
direct_edges AS (
    -- 每个记录和自身属于强关联，保证无关联的孤立点能被正常统计
    SELECT DISTINCT RecordID AS src_id, RecordID AS dst_id
    FROM record_contact
    UNION
    -- 同字段同值的不同记录标记为直接关联
    SELECT a.RecordID AS src_id, b.RecordID AS dst_id
    FROM record_contact a
    INNER JOIN record_contact b
        ON a.Field = b.Field 
        AND a.Value = b.Value
        AND a.RecordID != b.RecordID
),
-- 第二步：递归遍历所有连通路径，给每个记录分配所属组的ID（取组内最小的RecordID作为组唯一标识）
connected_groups AS (
    -- 递归初始状态：每个记录默认自身为一个独立组
    SELECT src_id AS record_id, src_id AS group_id
    FROM direct_edges
    WHERE src_id = dst_id
    UNION
    -- 递归扩展：如果两个记录直接关联，将组ID统一为两个组里更小的那个值
    SELECT e.dst_id AS record_id, LEAST(g.group_id, e.dst_id) AS group_id
    FROM connected_groups g
    INNER JOIN direct_edges e
        ON g.record_id = e.src_id
    WHERE LEAST(g.group_id, e.dst_id) < g.group_id -- 过滤重复计算，避免递归死循环
),
-- 第三步：去重，每个记录只保留最终归属的最小组ID
unique_groups AS (
    SELECT record_id, MIN(group_id) AS final_group_id
    FROM connected_groups
    GROUP BY record_id
)
-- 第四步：按组统计总记录数，输出最终结果
SELECT ug.record_id AS RecordID, COUNT(*) AS Count
FROM unique_groups ug
INNER JOIN unique_groups ug_all
    ON ug.final_group_id = ug_all.final_group_id
GROUP BY ug.record_id
ORDER BY ug.record_id;

结果说明 #

针对你给出的示例数据，上述语句执行后会完全返回期望结果：

• Record 1/2/4/5存在传递关联（1和2共享email、1和5共享phone、5和4共享email），同属一个组，Count值为4
• Record 3、6无任何关联记录，各自为独立组，Count值为1

注意：如果你使用的是不支持递归CTE的老旧数据库版本（比如MySQL 5.x），无法直接通过单条SQL实现该需求，需要通过存储过程循环遍历、或程序导出数据后计算图连通分量的方式实现。

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