如何编写SQL查询统计单表中拥有相同子记录集的父记录数量

核心思路 #

要统计每个父记录对应的相同子记录集的其他父记录数量，关键是先为每个父记录的子集合生成唯一标识，再基于这个标识统计同组的父记录总数，最终总数减1（排除自身）就是目标结果。

步骤1：生成子集合的唯一标识 #

通过GROUP_CONCAT将每个父记录的子ID按固定顺序拼接成字符串（必须排序，避免子ID顺序不同导致标识不同），或者用哈希函数（如MD5）将拼接后的字符串转为更简洁的哈希值，确保相同子集合得到完全一致的标识。

步骤2：统计同组父记录数量 #

基于生成的唯一标识，统计每个标识对应的父记录总数，再对每个父记录计算“总数-1”（排除自身），得到匹配的其他父记录数量。

完整SQL示例 #

假设表名为parent_child，包含字段parent_id（父记录ID）和child_id（子记录ID）：

方案一：直接关联匹配

WITH parent_child_sets AS (
    SELECT 
        parent_id,
        -- 按子ID排序后拼接，保证相同集合的字符串一致
        GROUP_CONCAT(child_id ORDER BY child_id) AS child_set
    FROM parent_child
    GROUP BY parent_id
)
SELECT 
    pcs.parent_id,
    COUNT(other_pcs.parent_id) AS matching_parent_count
FROM parent_child_sets pcs
-- 关联同子集合但不同父ID的记录
LEFT JOIN parent_child_sets other_pcs 
    ON pcs.child_set = other_pcs.child_set 
    AND pcs.parent_id != other_pcs.parent_id
GROUP BY pcs.parent_id
ORDER BY pcs.parent_id;

方案二：先统计组总数再计算

WITH parent_child_sets AS (
    SELECT 
        parent_id,
        MD5(GROUP_CONCAT(child_id ORDER BY child_id)) AS child_set_hash -- 用哈希减少字符串长度
    FROM parent_child
    GROUP BY parent_id
),
set_group_counts AS (
    SELECT 
        child_set_hash,
        COUNT(parent_id) AS total_parent_count
    FROM parent_child_sets
    GROUP BY child_set_hash
)
SELECT 
    pcs.parent_id,
    -- 组总数大于1时减1，否则为0
    CASE WHEN sgcs.total_parent_count > 1 THEN sgcs.total_parent_count - 1 ELSE 0 END AS matching_parent_count
FROM parent_child_sets pcs
JOIN set_group_counts sgcs ON pcs.child_set_hash = sgcs.child_set_hash
ORDER BY pcs.parent_id;

注意事项 #

• 若子ID数量较多，GROUP_CONCAT可能超出默认长度限制，需先调整会话参数：SET SESSION group_concat_max_len = 1000000;（根据实际需求设置长度）。
• 使用哈希函数（如MD5）可以避免长字符串的存储和比较问题，但需注意极低概率的哈希碰撞（业务场景中通常可忽略）。
• 若存在无任何子记录的父ID，上述查询会将所有无子记录的父ID归为同一组，统计结果为该组总数减1。

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