SQL优化：匹配分析场景下关联ID分组排序及去重查询求助

实现思路 #

你需要的排序本质是把两两关联的ID划分到同一个连通分量，相同连通分量的ID分到同一组，按组排序即可。主流支持递归CTE的数据库（MySQL 8.0+, PostgreSQL, Oracle, SQL Server等）都可以用以下方案实现：

具体SQL代码 #

假设你的原始匹配表名为match_pairs，代码如下：

WITH RECURSIVE all_ids AS (
    -- 先得到所有去重的ID和对应的NAME
    SELECT DISTINCT ID1 AS ID, NAME1 AS NAME FROM match_pairs
    UNION
    SELECT DISTINCT ID2 AS ID, NAME2 AS NAME FROM match_pairs
),
edges AS (
    -- 构造双向边，保证遍历的时候可以双向关联
    SELECT ID1 AS u, ID2 AS v FROM match_pairs
    UNION
    SELECT ID2 AS u, ID1 AS v FROM match_pairs
),
connected_components AS (
    -- 锚点：每个ID初始根节点为自身
    SELECT ID AS node, ID AS root_id FROM all_ids
    UNION ALL
    -- 递归：找到关联节点的最小根节点作为统一组标识
    SELECT e.v AS node, cc.root_id
    FROM connected_components cc
    JOIN edges e ON cc.node = e.u
    WHERE e.v < cc.root_id -- 用最小ID作为组号，避免无限递归
),
group_ids AS (
    -- 给每个ID分配最终的组号（同连通分量组号相同）
    SELECT node AS ID, MIN(root_id) AS group_no
    FROM connected_components
    GROUP BY node
)
-- 最终输出：按组大小倒序+组号排序，保证大组在前、同组连续展示
SELECT ai.ID, ai.NAME
FROM all_ids ai
JOIN group_ids gi ON ai.ID = gi.ID
ORDER BY COUNT(*) OVER (PARTITION BY gi.group_no) DESC, gi.group_no, ai.ID;

代码说明 #

• 递归CTE会自动把所有存在直接/间接关联的ID分到同一个组，用组内最小的ID作为统一组号
• 排序逻辑默认按分组大小倒序，和你给出的预期输出顺序完全对齐，如果你不需要大组优先，去掉COUNT(*) OVER (PARTITION BY gi.group_no) DESC,即可
• 组内顺序你可以根据需求调整，示例里额外加了ai.ID保证组内按ID排序，不需要可以直接删除
• 如果你的数据库不支持递归CTE（比如MySQL 5.x及以下版本），可以用存储过程循环遍历关联关系实现相同的分组逻辑，核心思路一致。

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