如何按分组查询与组内所有其他ID相连的目标ID？

需求说明 #

• 需针对每个分组单独处理，找出分组中与组内所有其他ID都相连的ID
• 示例结果：分组A输出ID1、2；分组B输出ID5；分组C输出ID8；分组D无符合条件ID，最终结果为1、2、5、8

现有无分组实现SQL #

SELECT ID FROM <table> 
where CONNECTED_TO in (select ID from <table>) 
group by ID
having count(*) = <number of records - 1>

分组场景解决方案SQL #

要实现分组逻辑，核心是先统计每个分组内的总ID数量，再对比每个ID在组内连接的不同ID数量是否等于「分组总ID数-1」（排除自身），同时确保连接的ID都属于当前分组。

方法一：子查询统计分组总ID数 #

SELECT t.ID
FROM <table> t
-- 仅保留组内的连接关系
JOIN (SELECT DISTINCT group_col, ID FROM <table>) g 
  ON t.group_col = g.group_col AND t.CONNECTED_TO = g.ID
GROUP BY t.group_col, t.ID
HAVING COUNT(DISTINCT t.CONNECTED_TO) = (
    SELECT COUNT(DISTINCT ID) - 1 
    FROM <table> 
    WHERE group_col = t.group_col
)

方法二：窗口函数简化统计（适用于支持窗口函数的数据库：MySQL8.0+、PostgreSQL、SQL Server等） #

WITH group_summary AS (
    SELECT 
        group_col,
        ID,
        CONNECTED_TO,
        -- 提前计算当前分组的总ID数
        COUNT(DISTINCT ID) OVER (PARTITION BY group_col) AS total_group_ids
    FROM <table>
    -- 过滤掉连接到组外的无效记录
    WHERE CONNECTED_TO IN (SELECT ID FROM <table> WHERE group_col = <table>.group_col)
)
SELECT DISTINCT ID
FROM group_summary
GROUP BY group_col, ID
HAVING COUNT(DISTINCT CONNECTED_TO) = total_group_ids - 1

关键说明 #

• 将代码中的 group_col 替换为你实际的分组字段名称
• 使用 COUNT(DISTINCT CONNECTED_TO) 避免同一ID重复连接的情况干扰统计结果
• 过滤条件确保仅统计组内的连接关系，排除组外ID的影响

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