PostgreSQL插入脚本需求：每人最多1条记录，文件分配给同国未分配用户

原查询的核心问题是person和get_files按国家关联时会产生笛卡尔积，导致同一用户匹配所有同国文件——虽然ON CONFLICT (name) DO NOTHING会跳过重复插入，但该用户的第一条记录插入后，其余同文件的匹配结果全被跳过，而同国其他用户根本没机会被匹配到。

要实现「同国用户按顺序分配文件，每人最多1条」的逻辑，需要将未分配用户和待分配文件按国家分组后，进行一对一匹配：

修正后的SQL脚本 #

WITH unassigned_persons AS (
    -- 筛选未分配过文件的用户，按国家分组并排序
    SELECT
        name,
        age,
        country,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY name) AS user_rank
    FROM person
    WHERE name NOT IN (SELECT name FROM history_person_file)
),
available_files AS (
    -- 筛选待分配文件，按国家分组并排序
    SELECT
        type,
        country,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY type) AS file_rank
    FROM files
    WHERE title IS NOT NULL
)
INSERT INTO history_person_file (name, document_type, age)
SELECT
    up.name,
    af.type,
    up.age
FROM unassigned_persons up
JOIN available_files af
    ON up.country = af.country
    AND up.user_rank = af.file_rank -- 同国家内，用户与文件按序号一对一匹配
ON CONFLICT (name) DO NOTHING;

关键逻辑说明 #

• unassigned_persons CTE：仅保留未分配过文件的用户，用ROW_NUMBER()给每个国家的用户分配序号，确保同国用户有明确的分配顺序（示例按name排序，可按需换成id或其他字段）。
• available_files CTE：给每个国家的待分配文件也分配序号，排序规则可根据实际需求调整。
• 关联条件：通过country和序号user_rank = file_rank实现同国家内用户与文件的一对一匹配，彻底避免一个用户匹配多个文件的情况。
• ON CONFLICT (name) DO NOTHING：作为并发场景下的最后一层保障，防止重复插入。

扩展说明 #

如果同国用户数和文件数不一致，该脚本会优先匹配数量少的一方（比如用户3人、文件5个，则仅3个文件被分配；反之则仅3个用户能拿到文件），完全符合「每人最多1条」的需求。如果需要循环分配（文件数多于用户数时允许用户重复分配），可将序号逻辑改为MOD取余，但这不符合当前需求，故未纳入脚本。

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