Postgres实现每组选唯一行且无重复父/子项及组合

嘿，我来帮你搞定这个父项和子项唯一匹配的需求！咱们先把需求再理清楚：要给每个父项匹配一个子项，绝对不能重复父项、子项，也不能重复已经用过的父-子组合；要是遇到父项比子项多的情况（比如6个父对应3个子），那最多返回和子项数量一样的行数，每个父/子都不重复，只取父项能匹配到的第一个子项就行。

核心思路 #

咱们可以用窗口函数给父项和子项分别排序，确保每个父项只选第一个候选子项，同时每个子项只被第一个符合条件的父项选中，再结合过滤条件排除已有的组合，就能实现需求了。

示例SQL实现（以关联表场景为例） #

假设你有一个记录所有可能父-子配对的表parent_child（结构是parent_id, child_id），还有一个existing_matches表用来记录已经用过的父-子组合，那查询可以这么写：

WITH ranked_parent_child AS (
    SELECT 
        pc.parent_id,
        pc.child_id,
        -- 给每个父项的子项排序，确保取第一个候选
        ROW_NUMBER() OVER (PARTITION BY pc.parent_id ORDER BY pc.child_id) AS parent_row_num,
        -- 给每个子项的父项排序，确保每个子项只被选一次
        ROW_NUMBER() OVER (PARTITION BY pc.child_id ORDER BY pc.parent_id) AS child_row_num
    FROM parent_child pc
    -- 排除已经用过的父-子组合
    LEFT JOIN existing_matches em 
        ON pc.parent_id = em.parent_id AND pc.child_id = em.child_id
    WHERE em.parent_id IS NULL
)
SELECT parent_id, child_id
FROM ranked_parent_child
-- 筛选出每个父项的第一个子项，同时保证子项只被一个父项选中
WHERE parent_row_num = 1 AND child_row_num = 1
-- 当父项多于子项时，限制返回行数等于子项的唯一数量
LIMIT (SELECT COUNT(DISTINCT child_id) FROM parent_child);

逻辑解释 #

1. CTE部分：先给每个父项的所有可选子项排号（按子项ID升序，你也可以改成按优先级、创建时间等规则），同时给每个子项的所有可选父项排号。这样就能确保每个父项只拿第一个子项，每个子项只被第一个父项选中。
2. 排除已有组合：通过左连接existing_matches表，过滤掉已经用过的父-子配对，避免重复。
3. 筛选与限制：最后只留同时满足“父项第一个子项”和“子项第一个父项”的记录，再用LIMIT限制返回行数等于子项的数量，完美解决6父3子这种父多子少的场景。

如果是父表和子表直接关联的场景 #

要是你没有专门的关联表，只有parents表（存父项ID）和children表（存子项ID），可以用交叉连接生成所有可能配对，再用同样的逻辑处理：

WITH parent_child_pairs AS (
    SELECT 
        p.parent_id,
        c.child_id,
        ROW_NUMBER() OVER (PARTITION BY p.parent_id ORDER BY c.child_id) AS parent_row_num,
        ROW_NUMBER() OVER (PARTITION BY c.child_id ORDER BY p.parent_id) AS child_row_num
    FROM parents p
    CROSS JOIN children c
    -- 这里可以加额外过滤条件，比如某些父不能匹配某些子
)
SELECT parent_id, child_id
FROM parent_child_pairs
WHERE parent_row_num = 1 AND child_row_num = 1
LIMIT (SELECT COUNT(child_id) FROM children);

这个方案完全符合你的需求：父项、子项、父-子组合都不重复，父多子少时自动限制返回行数，而且可以灵活调整排序规则来选择“首个子项”。

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