MySQL一对多场景下按分组排序实现两表记录1:1关联更新

现有表结构与测试数据 #

properties（房产表） #

存储房产基础信息，包含待填充的agent_id字段，测试数据如下：

INSERT INTO properties (property_id, year_built, type_id, code)
VALUES 
(1, 2000, 3, 'ABC'),
(2, 2001, 3, 'ABC'),
(3, 2002, 3, 'ABC'),
(4, 2003, 3, 'ABC'),
(5, 2004, 3, 'ABC'),
(6, 2005, 3, 'ABC'),
(7, 2000, 3, 'DEF'),
(8, 2001, 3, 'DEF'),
(9, 2002, 3, 'DEF'),
(10, 2003, 3, 'DEF'),
(11, 2004, 3, 'DEF'),
(12, 2005, 3, 'DEF'),
(13, 2000, 3, 'GHI'),
(14, 2001, 3, 'GHI'),
(15, 2002, 3, 'GHI'),
(16, 2003, 3, 'GHI'),
(17, 2004, 3, 'GHI'),
(18, 2005, 3, 'GHI');

agents（经纪人表） #

记录总数与properties表完全一致，测试数据如下：

INSERT INTO agents (agent_id, year_built, type_id)
VALUES 
(50, 2000, 3),
(51, 2001, 3),
(52, 2002, 3),
(53, 2003, 3),
(54, 2004, 3),
(55, 2005, 3),
(56, 2000, 3),
(57, 2001, 3),
(58, 2002, 3),
(59, 2003, 3),
(60, 2004, 3),
(61, 2005, 3),
(62, 2000, 3),
(63, 2001, 3),
(64, 2002, 3),
(65, 2003, 3),
(66, 2004, 3),
(67, 2005, 3);

需求与问题说明 #

• 匹配规则：同year_built（建成年份）、同type_id（类型ID）的房产与经纪人才能匹配；同分组下按升序排列的第n条property_id记录，对应匹配同分组按升序排列的第n条agent_id记录
• 约束：不允许修改两张表的任何字段、键、属性
• 普通JOIN写法的问题：仅用年份、类型关联会产生笛卡尔积，单条房产会匹配到同组所有经纪人，出现重复结果，示例错误查询与结果如下：

SELECT properties.*, agents.agent_id
FROM   properties
JOIN   agents
       USING(year_built, type_id)
WHERE  properties.year_built = 2000;

property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
1            2000        3        ABC   56
1            2000        3        ABC   62
7            2000        3        DEF   50
7            2000        3        DEF   56
7            2000        3        DEF   62
13           2000        3        GHI   50
13           2000        3        GHI   56
13           2000        3        GHI   62

• 预期匹配结果（2000年房产为例）：

property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
7            2000        3        DEF   56
13           2000        3        GHI   62

实现方法 #

核心逻辑是用窗口函数ROW_NUMBER()给两张表同分组内的记录按排序规则生成连续行号，关联时除了年份、类型条件，额外匹配行号即可实现一一对应，不会产生重复匹配。

1. 匹配结果验证查询 #

先执行以下查询确认匹配结果符合预期：

WITH property_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY year_built, type_id 
            ORDER BY property_id
        ) AS rn
    FROM properties
),
agent_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY year_built, type_id 
            ORDER BY agent_id
        ) AS rn
    FROM agents
)
SELECT 
    p.property_id,
    p.year_built,
    p.type_id,
    p.code,
    a.agent_id
FROM property_ranked p
JOIN agent_ranked a
    ON p.year_built = a.year_built
    AND p.type_id = a.type_id
    AND p.rn = a.rn;

2. 更新agent_id字段 #

确认结果无误后，执行更新语句填充properties表的agent_id字段（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的主流数据库）：

WITH property_ranked AS (
    SELECT 
        property_id,
        ROW_NUMBER() OVER (
            PARTITION BY year_built, type_id 
            ORDER BY property_id
        ) AS rn,
        year_built,
        type_id
    FROM properties
),
agent_ranked AS (
    SELECT 
        agent_id,
        ROW_NUMBER() OVER (
            PARTITION BY year_built, type_id 
            ORDER BY agent_id
        ) AS rn,
        year_built,
        type_id
    FROM agents
)
UPDATE properties
JOIN property_ranked p ON properties.property_id = p.property_id
JOIN agent_ranked a 
    ON p.year_built = a.year_built
    AND p.type_id = a.type_id
    AND p.rn = a.rn
SET properties.agent_id = a.agent_id;

逻辑说明：两个CTE分别对房产、经纪人按年份+类型分组，组内按主键升序生成从1开始的连续序号，关联时匹配同序号的记录，完全符合“组内按顺序一一对应”的规则，全程仅查询和更新agent_id字段值，不会修改任何表结构。

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