如何用SQL实现NAME的两级深度传递关联关系查询？

表结构 #

table1的表结构如下：

+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| ID      | int             | NO   | PRI | NULL    | auto_increment |
| NAME    | bigint unsigned | NO   | MUL | NULL    |                |
| SECONDS | int             | NO   | MUL | NULL    |                |
| LINK    | int             | YES  |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+

示例数据 #

table1中的示例数据：

+----+---------+---------+---------+
| ID | NAME    | SECONDS | LINK    |
+----+---------+---------+---------+
|  1 |       1 |       1 |       1 |
|  2 |       2 |       1 |       1 |
|  3 |       3 |       1 |       2 |
|  4 |       4 |       1 |       2 |
|  5 |       2 |       2 |       1 |
|  6 |       3 |       2 |       1 |
|  7 |       4 |       2 |       2 |
|  8 |       1 |       2 |       3 |
|  9 |       3 |       3 |       1 |
| 10 |       4 |       3 |       1 |
| 11 |       1 |       3 |       2 |
+----+---------+---------+---------+

目标结果 #

当输入NAME为1时，需要返回的结果：

+---------+---------+---------+
| NAME    | SECONDS | LINK    |
+---------+---------+---------+
|       1 |       1 |       1 |
|       1 |       2 |       3 |
|       1 |       3 |       2 |
|       2 |       1 |       1 |
|       2 |       2 |       1 |
|       3 |       1 |       2 |
|       3 |       2 |       1 |
|       3 |       3 |       1 |
+---------+---------+---------+

分组规则说明 #

我们将共享相同SECONDS和LINK的NAME视为同一分组，可通过以下SQL查询所有包含多个NAME的分组：

SELECT
  GROUP_CONCAT(DISTINCT NAME) AS NAME_list,
  SECONDS,
  LINK    
FROM
  table1
GROUP BY
  SECONDS,
  LINK    
HAVING
  COUNT(DISTINCT NAME) > 1;

该查询返回结果：

+--------------+---------+---------+
| NAME_list    | SECONDS | LINK    |
+--------------+---------+---------+
| 1,2          |       1 |       1 |
| 3,4          |       1 |       2 |
| 2,3          |       2 |       1 |
| 3,4          |       3 |       1 |
+--------------+---------+---------+

需求说明 #

给定输入NAME列表（例如(1)），需实现：

1. 找到该NAME所在分组中的其他NAME（如1所在分组的2）
2. 再找到这些NAME在其他分组中关联的NAME（如2所在分组的3）
3. 仅保留两级深度的传递关系（即A->B->C），不深入第三级（如1->2->3->4无需返回）
   最终返回所有符合该传递关系的NAME对应的行。

实现SQL #

我们可以通过CTE（公共表表达式）分两步获取关联NAME，最后汇总结果：

-- 假设输入NAME为1，可根据需求替换为其他值或列表
WITH first_level AS (
    -- 第一步：获取输入NAME所在分组的所有其他NAME（第一级关联）
    SELECT DISTINCT t2.NAME
    FROM table1 t1
    JOIN table1 t2 ON t1.SECONDS = t2.SECONDS AND t1.LINK = t2.LINK
    WHERE t1.NAME = 1 
      AND t2.NAME != 1 -- 排除输入NAME本身
),
second_level AS (
    -- 第二步：获取第一级关联NAME所在分组的所有其他NAME（第二级关联），排除输入NAME避免循环
    SELECT DISTINCT t3.NAME
    FROM first_level fl
    JOIN table1 t2 ON fl.NAME = t2.NAME
    JOIN table1 t3 ON t2.SECONDS = t3.SECONDS AND t2.LINK = t3.LINK
    WHERE t3.NAME != 1
)
-- 汇总输入NAME、第一级、第二级关联NAME的所有行，去重并排序
SELECT DISTINCT t.NAME, t.SECONDS, t.LINK
FROM table1 t
WHERE t.NAME = 1 
   OR t.NAME IN (SELECT NAME FROM first_level)
   OR t.NAME IN (SELECT NAME FROM second_level)
ORDER BY t.NAME, t.SECONDS;

逻辑说明 #

• first_level：通过关联同一SECONDS+LINK分组，筛选出和输入NAME同组的其他NAME，得到第一级关联对象。
• second_level：基于第一级关联对象，再次关联分组，筛选出他们的关联对象，同时排除输入NAME，避免出现循环引用。
• 最后查询所有属于输入NAME、第一级、第二级关联的行，去重后排序，即可得到目标结果。

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