MSSQL环境下基于Knex.js处理Brokers表重复行并关联BrokerIndividuals表的技术方案问询

针对你提到的需求——处理brokers表重复数据、关联更新brokerIndividuals外键，以及创建带过滤条件的唯一索引（Knex暂不支持，用原生SQL实现），我整理了一套分步执行的方案，适配MSSQL 6.0.1版本：

1. 先定位重复的活跃经纪公司记录 #

首先用窗口函数找出所有companyName重复且isDeleted为NULL的行，标记出每组里需要被软删除的重复项：

WITH DuplicateBrokers AS (
    SELECT 
        brokerId,
        companyName,
        isDeleted,
        -- 按brokerId排序，每组第一条标记为rowNum=1，其余为>1
        ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY brokerId) AS rowNum
    FROM brokers
    WHERE isDeleted IS NULL
)
-- 查看所有需要处理的重复行
SELECT * FROM DuplicateBrokers WHERE rowNum > 1;

执行这个查询可以先确认要处理的目标数据，避免误操作。

2. 软删除重复的经纪公司记录 #

确认重复数据后，将每组中除第一条外的记录isDeleted设为true（这里假设isDeleted是bit类型，MSSQL中1代表true，如果是其他类型请自行调整值）：

WITH DuplicateBrokers AS (
    SELECT 
        brokerId,
        ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY brokerId) AS rowNum
    FROM brokers
    WHERE isDeleted IS NULL
)
UPDATE brokers
SET isDeleted = 1
FROM brokers
JOIN DuplicateBrokers ON brokers.brokerId = DuplicateBrokers.brokerId
WHERE DuplicateBrokers.rowNum > 1;

3. 更新关联的经纪人个体外键 #

接下来把关联到被软删除经纪公司的brokerIndividuals记录，外键更新到每组保留的第一条经纪公司主键：

WITH BrokerGroups AS (
    -- 获取每个companyName对应的主记录（保留的第一条）
    SELECT 
        brokerId AS mainBrokerId,
        companyName
    FROM (
        SELECT 
            brokerId,
            companyName,
            ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY brokerId) AS rowNum
        FROM brokers
        WHERE isDeleted IS NULL
    ) AS ranked
    WHERE rowNum = 1
),
DeletedDuplicates AS (
    -- 获取被软删除的重复经纪公司记录
    SELECT 
        brokerId,
        companyName
    FROM brokers
    WHERE isDeleted = 1
)
UPDATE bi
SET bi.brokerId = bg.mainBrokerId
FROM brokerIndividuals bi
JOIN DeletedDuplicates dd ON bi.brokerId = dd.brokerId
JOIN BrokerGroups bg ON dd.companyName = bg.companyName;

4. 创建带过滤条件的唯一索引 #

最后创建MSSQL支持的过滤唯一索引，保证isDeleted为NULL的记录中companyName唯一：

CREATE UNIQUE NONCLUSTERED INDEX IX_brokers_companyName_Unique_Active
ON brokers (companyName)
WHERE isDeleted IS NULL;

这个索引会阻止后续插入/更新出现companyName重复且isDeleted为NULL的记录。

在Knex.js中执行原生SQL #

因为Knex暂不支持这种带WHERE子句的唯一索引，所以需要用knex.raw()执行上述原生SQL，示例代码如下：

// 软删除重复行
await knex.raw(`
    WITH DuplicateBrokers AS (
        SELECT 
            brokerId,
            ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY brokerId) AS rowNum
        FROM brokers
        WHERE isDeleted IS NULL
    )
    UPDATE brokers
    SET isDeleted = 1
    FROM brokers
    JOIN DuplicateBrokers ON brokers.brokerId = DuplicateBrokers.brokerId
    WHERE DuplicateBrokers.rowNum > 1;
`);

// 更新brokerIndividuals外键
await knex.raw(`
    WITH BrokerGroups AS (
        SELECT 
            brokerId AS mainBrokerId,
            companyName
        FROM (
            SELECT 
                brokerId,
                companyName,
                ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY brokerId) AS rowNum
            FROM brokers
            WHERE isDeleted IS NULL
        ) AS ranked
        WHERE rowNum = 1
    ),
    DeletedDuplicates AS (
        SELECT 
            brokerId,
            companyName
        FROM brokers
        WHERE isDeleted = 1
    )
    UPDATE bi
    SET bi.brokerId = bg.mainBrokerId
    FROM brokerIndividuals bi
    JOIN DeletedDuplicates dd ON bi.brokerId = dd.brokerId
    JOIN BrokerGroups bg ON dd.companyName = bg.companyName;
`);

// 创建过滤唯一索引
await knex.raw(`
    CREATE UNIQUE NONCLUSTERED INDEX IX_brokers_companyName_Unique_Active
    ON brokers (companyName)
    WHERE isDeleted IS NULL;
`);

注意：执行所有操作前，请务必在测试环境验证，并且备份数据，防止数据丢失或误修改。

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