PostgreSQL中限制中间表关联记录数量的实现方法

要实现每个Table A记录最多关联100条Table B记录，同时每个Table B记录最多关联100条Table A记录，不能用全局LIMIT（会限制总记录数），得用**窗口函数ROW_NUMBER()**按分组来控制每组的关联数量。

具体SQL实现 #

WITH matched_pairs AS (
    SELECT
        a.id AS a_id,
        b.id AS b_id,
        -- 按A的id分组，给匹配的B记录编号，只保留前100条
        ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) AS rn_a,
        -- 按B的id分组，给匹配的A记录编号，只保留前100条
        ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY a.id) AS rn_b
    FROM table_a a
    JOIN table_b b ON a.name = b.name
)
INSERT INTO intermediate_table (a_id, b_id)
SELECT a_id, b_id
FROM matched_pairs
WHERE rn_a <= 100  -- 每个A最多关联100个B
  AND rn_b <= 100; -- 每个B最多关联100个A

关键说明 #

• PARTITION BY a.id 会把同一个A的所有匹配B记录归为一组，ROW_NUMBER() 按指定规则（示例中用b.id排序）给组内记录编号，筛选rn_a <= 100就能保证每个A只关联前100个匹配的B。
• PARTITION BY b.id 逻辑相同，保证每个B最多关联100个匹配的A。
• 排序字段可以按需调整，比如要取最新的100条，可把ORDER BY b.id换成ORDER BY b.created_at DESC（假设B表有created_at字段）。
• 如果只需要单向限制（比如只控制A的关联数，不管B），去掉对应的rn_b <= 100条件即可。

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