如何用存储过程将tblTransaction记录均衡分配至技能矩阵表

我来帮你搞定这个基于tblTransaction和tblSkillMatrix的记录分配逻辑，最终把结果写入tblAssignment表。先把整个流程和核心规则理清楚：

涉及表结构 #

tblTransaction #

ID Owner Country Region Vendor Description
----------------------------------------------------------------
1  NULL  Country1 Region1 Vendor2
4  NULL  Country1 Region1 NULL
7  NULL  Country1 Region1 Vendor2
10 NULL  Country1 Region1 NULL
13 NULL  Country1 Region2 Vendor3
16 NULL  Country1 Region2 Vendor3 Description1
19 NULL  Country1 Region2 Vendor3 Description1
2  NULL  Country2 Region1 Vendor1 Description1
5  NULL  Country2 Region2 Vendor1
8  NULL  Country2 Region1 Vendor1
11 NULL  Country2 Region1 Vendor2 Description1
14 NULL  Country2 Region1 NULL
17 NULL  Country2 Region1 Vendor2
20 NULL  Country3 Region1 NULL
3  NULL  Country3 Region2 Vendor3
6  NULL  Country3 Region2 Vendor3 Description1
9  NULL  Country3 Region2 NULL
12 NULL  Country3 Region2 NULL
15 NULL  Country3 Region1 Vendor1
18 NULL  Country3 Region2 Vendor1

tblSkillMatrix #

Owner    IsTeamLead Country Vendor  Region  Description
-------------------------------------------------------------------
Person1  N          Country1 Vendor1 Region1
Person2  Y          Country2 Vendor2 Region2 Description1
Person3  N          Country2 Vendor3 Region2
Person4  Y          Country1 Vendor4 Region1 Description1
Person5  N          Country1 Vendor4 Region1

核心分配规则 #

• 带有Description = 'Description1'的记录，优先分配给团队负责人（IsTeamLead = 'Y'）
• 若tblSkillMatrix中无对应Country且Vendor为NULL，这类记录要均衡分配给所有团队负责人，不受Country限制
• 每个Country下，分配给团队负责人的交易记录最多2条
• 非团队负责人的分配，需按Country维度均衡分配
• 非团队负责人分配时，若tblSkillMatrix中无对应Country，则改用Region维度均衡分配

目标输出表 tblAssignment #

ID Owner    Country Region Vendor  Description
----------------------------------------------------------------
1  Person1  Country1 Region1 Vendor2
4  Person1  Country1 Region1 NULL
7  Person5  Country1 Region1 Vendor2
10 Person5  Country1 Region1 NULL
13 Person4  Country1 Region2 Vendor3
16 Person2  Country1 Region2 Vendor3 Description1
19 Person2  Country1 Region2 Vendor3 Description1
2  Person2  Country2 Region1 Vendor1 Description1
5  Person2  Country2 Region2 Vendor1
8  Person2  Country2 Region1 Vendor1
11 Person4  Country2 Region1 Vendor2 Description1
14 Person3  Country2 Region1 NULL
17 Person3  Country2 Region1 Vendor2
20 Person4  Country3 Region1 NULL
3  Person3  Country3 Region2 Vendor3
6  Person4  Country3 Region2 Vendor3 Description1
9  Person4  Country3 Region2 NULL
12 Person2  Country3 Region2 NULL
15 Person1  Country3 Region1 Vendor1
18 Person3  Country3 Region2 Vendor1

存储过程实现代码 #

下面是适配SQL Server的存储过程，代码里加了注释说明关键逻辑：

CREATE PROCEDURE AssignTransactionsToOwners
AS
BEGIN
    SET NOCOUNT ON;

    -- 清空目标表（支持重复执行）
    TRUNCATE TABLE tblAssignment;

    -- 临时表拆分团队负责人和非团队负责人，简化后续判断
    CREATE TABLE #TeamLeaders (
        Owner NVARCHAR(50),
        Country NVARCHAR(50),
        Vendor NVARCHAR(50),
        Region NVARCHAR(50),
        Description NVARCHAR(100)
    );

    CREATE TABLE #NonTeamLeaders (
        Owner NVARCHAR(50),
        Country NVARCHAR(50),
        Vendor NVARCHAR(50),
        Region NVARCHAR(50),
        Description NVARCHAR(100)
    );

    INSERT INTO #TeamLeaders
    SELECT Owner, Country, Vendor, Region, Description
    FROM tblSkillMatrix
    WHERE IsTeamLead = 'Y';

    INSERT INTO #NonTeamLeaders
    SELECT Owner, Country, Vendor, Region, Description
    FROM tblSkillMatrix
    WHERE IsTeamLead = 'N';

    -- 步骤1：处理Description=Description1的记录，分配给团队负责人，控制每个Country最多2条
    WITH RankedSpecialTransactions AS (
        SELECT 
            t.ID, t.Country, t.Region, t.Vendor, t.Description,
            ROW_NUMBER() OVER (PARTITION BY t.Country ORDER BY NEWID()) AS rn
        FROM tblTransaction t
        WHERE t.Description = 'Description1'
    ),
    LeaderSpecialAssignments AS (
        SELECT 
            rst.ID,
            (SELECT TOP 1 tl.Owner 
             FROM #TeamLeaders tl 
             WHERE (tl.Country = rst.Country OR rst.Country NOT IN (SELECT Country FROM #TeamLeaders))
             ORDER BY NEWID()) AS Owner,
            rst.Country, rst.Region, rst.Vendor, rst.Description
        FROM RankedSpecialTransactions rst
        WHERE rst.rn <= 2
    )
    INSERT INTO tblAssignment (ID, Owner, Country, Region, Vendor, Description)
    SELECT ID, Owner, Country, Region, Vendor, Description
    FROM LeaderSpecialAssignments;

    -- 记录已分配的ID，避免重复分配
    DECLARE @AssignedIDs TABLE (ID INT);
    INSERT INTO @AssignedIDs SELECT ID FROM tblAssignment;

    -- 步骤2：处理Vendor为NULL且无对应Country的记录，均衡分配给团队负责人
    WITH VendorNullNoCountry AS (
        SELECT 
            t.ID, t.Country, t.Region, t.Vendor, t.Description,
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
            (SELECT COUNT(*) FROM #TeamLeaders) AS LeaderCount
        FROM tblTransaction t
        WHERE t.ID NOT IN (SELECT ID FROM @AssignedIDs)
          AND t.Vendor IS NULL
          AND t.Country NOT IN (SELECT Country FROM tblSkillMatrix)
    )
    INSERT INTO tblAssignment (ID, Owner, Country, Region, Vendor, Description)
    SELECT 
        vnnc.ID,
        (SELECT Owner FROM #TeamLeaders ORDER BY NEWID() OFFSET (vnnc.rn - 1) % vnnc.LeaderCount ROWS FETCH NEXT 1 ROW ONLY) AS Owner,
        vnnc.Country, vnnc.Region, vnnc.Vendor, vnnc.Description
    FROM VendorNullNoCountry vnnc;

    -- 更新已分配ID列表
    INSERT INTO @AssignedIDs SELECT ID FROM VendorNullNoCountry;

    -- 步骤3：处理剩余记录，分配给非团队负责人，优先按Country均衡，无对应Country则按Region
    WITH RemainingTransactions AS (
        SELECT 
            t.ID, t.Country, t.Region, t.Vendor, t.Description,
            -- 确定分组键：有对应Country用Country，否则用Region
            CASE 
                WHEN EXISTS (SELECT 1 FROM #NonTeamLeaders ntl WHERE ntl.Country = t.Country) THEN t.Country
                ELSE t.Region
            END AS GroupKey
        FROM tblTransaction t
        WHERE t.ID NOT IN (SELECT ID FROM @AssignedIDs)
    ),
    RankedRemaining AS (
        SELECT 
            rt.ID, rt.Country, rt.Region, rt.Vendor, rt.Description, rt.GroupKey,
            ROW_NUMBER() OVER (PARTITION BY rt.GroupKey ORDER BY NEWID()) AS rn
        FROM RemainingTransactions rt
    ),
    NonLeaderAssignments AS (
        SELECT 
            rr.ID,
            (SELECT TOP 1 ntl.Owner 
             FROM #NonTeamLeaders ntl 
             WHERE (rr.GroupKey = ntl.Country OR rr.GroupKey = ntl.Region)
             ORDER BY NEWID()) AS Owner,
            rr.Country, rr.Region, rr.Vendor, rr.Description
        FROM RankedRemaining rr
    )
    INSERT INTO tblAssignment (ID, Owner, Country, Region, Vendor, Description)
    SELECT ID, Owner, Country, Region, Vendor, Description
    FROM NonLeaderAssignments;

    -- 清理临时表
    DROP TABLE #TeamLeaders;
    DROP TABLE #NonTeamLeaders;

    PRINT '交易记录分配完成，结果已写入tblAssignment表';
END
GO

关键逻辑说明 #

1. 临时表拆分：把团队负责人和非团队负责人分开存储，减少后续重复查询和条件判断的复杂度
2. 特殊记录优先处理：先分配带有指定描述的记录给团队负责人，同时严格控制每个国家的分配上限
3. 极端场景适配：针对无对应国家且供应商为空的记录，用取模方式实现团队负责人之间的均衡分配
4. 分层均衡分配：非团队负责人的分配先按国家分组，没有匹配国家时自动降级到区域分组，保证分配的公平性

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