SQL Server：单条UPDATE语句实现本地与远程客户唯一关联

表结构 #

remote_clients表 #

CREATE TABLE remote_clients (
   id INT NOT NULL IDENTITY PRIMARY KEY,
   first_name VARCHAR(255), 
   last_name VARCHAR(255),
   date_of_birth DATE
)

local_clients表 #

CREATE TABLE local_clients (
   id INT NOT NULL IDENTITY PRIMARY KEY,
   first_name VARCHAR(255), 
   last_name VARCHAR(255),
   date_of_birth DATE,
   remote_client_id INT FOREIGN KEY REFERENCES remote_clients(id) 
)

需求 #

将local_clients中所有记录与remote_clients里姓名和出生日期完全匹配的记录关联，但每个remote_clients记录只能被一个local_clients记录绑定，要求用单条UPDATE语句实现。

示例数据 #

插入local_clients数据 #

INSERT INTO local_clients
  (first_name, last_name, date_of_birth)
VALUES
  ('foo', 'bar', '2020-01-01'),
  ('foo', 'bar', '2020-01-01'),
  ('baz', 'lurman', '2020-01-01'),
  ('steve', 'last', '2020-01-01'),
  ('steve', 'last', '2020-01-01'),
  ('aaron', 'something', '2020-01-01')

插入remote_clients数据 #

INSERT INTO remote_clients
  (first_name, last_name, date_of_birth)
VALUES
  ('foo', 'bar', '2020-01-01'),
  ('foo', 'bar', '2020-01-01'),
  ('baz', 'lurman', '2020-01-01'),
  ('baz', 'lurman', '2020-01-01'),
  ('steve', 'last', '2020-01-01'),

期望更新结果 #

解决方案 #

使用窗口函数为两组数据按匹配条件分组后添加序号，再通过序号匹配实现一对一关联更新，适用于SQL Server的语句如下：

WITH LocalClientsWithRowNum AS (
    SELECT 
        id,
        remote_client_id,
        ROW_NUMBER() OVER (PARTITION BY first_name, last_name, date_of_birth ORDER BY id) AS row_num
    FROM local_clients
),
RemoteClientsWithRowNum AS (
    SELECT 
        id,
        first_name,
        last_name,
        date_of_birth,
        ROW_NUMBER() OVER (PARTITION BY first_name, last_name, date_of_birth ORDER BY id) AS row_num
    FROM remote_clients
)
UPDATE l
SET l.remote_client_id = r.id
FROM LocalClientsWithRowNum l
JOIN RemoteClientsWithRowNum r 
    ON l.first_name = r.first_name
    AND l.last_name = r.last_name
    AND l.date_of_birth = r.date_of_birth
    AND l.row_num = r.row_num

逻辑说明 #

1. 为local_clients按姓名、出生日期分组，每组内按id排序生成唯一序号row_num，同组内的本地客户序号不重复。
2. 为remote_clients执行相同的分组和序号生成操作，确保同组内的远程客户序号唯一。
3. 通过姓名、出生日期匹配+序号相等的条件，实现本地客户与远程客户的一对一绑定，每个远程客户仅会被匹配一次。

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