将案例随机分配改为循环分配的SQL实现咨询

Got it, let's work through this round-robin assignment problem. You want to evenly distribute 100 (and future) cases to 5 technicians in a cyclic way—first 5 cases go to each tech in order, then the 6th loops back to the first tech, and so on. Here's a practical, fair SQL solution tailored to your tables.

Core Logic #

The key is to:

• Assign a fixed "rank" to each technician (1 to 5, based on their ID or any consistent order).
• Assign a sequential rank to each case (ordered by case ID, or creation time if you have that field).
• Use modulo arithmetic to map each case's rank to a technician's rank—this creates the cyclic pattern. For example:
  • Case rank 1 → Tech rank 1
  • Case rank 5 → Tech rank 5
  • Case rank 6 → (6-1) % 5 +1 = 1 → Tech rank 1

1. One-Time Assignment for Existing 100 Cases #

First, let's assign all existing cases to technicians in the cyclic order. We'll use CTEs to rank both technicians and cases, then join them to update the case table (I'll assume you add an ID_TEC_ASSIGNED field to your case table to store the assigned tech ID):

-- Add the assignment field if you don't have it already
ALTER TABLE 案例表 ADD COLUMN ID_TEC_ASSIGNED INT;

-- Perform the cyclic assignment
WITH RankedTechnicians AS (
    SELECT 
        ID_TEC,
        ROW_NUMBER() OVER (ORDER BY ID_TEC) AS TechRank -- Assign fixed rank to each tech
    FROM Technicians
),
RankedCases AS (
    SELECT 
        ID_CASE,
        ROW_NUMBER() OVER (ORDER BY ID_CASE) AS CaseRank -- Rank cases by their ID (use creation time if available)
    FROM 案例表
)
UPDATE 案例表 c
SET ID_TEC_ASSIGNED = rt.ID_TEC
FROM 案例表 c
JOIN RankedCases rc ON c.ID_CASE = rc.ID_CASE
JOIN RankedTechnicians rt ON MOD(rc.CaseRank - 1, (SELECT COUNT(*) FROM Technicians)) + 1 = rt.TechRank;

Notes: #

• If you have a CREATION_DATE field in the case table, replace ORDER BY ID_CASE with ORDER BY CREATION_DATE in the RankedCases CTE—this ensures cases are assigned in the order they were created, which is more logical for new cases.
• The MOD(rc.CaseRank -1, total_techs) +1 formula adjusts the modulo result to match our 1-based rank system (instead of 0-based).

2. Automatic Assignment for New Cases #

To handle future cases automatically as they come in, use a database trigger. Below is an example for MySQL (adjust syntax slightly for SQL Server, PostgreSQL, etc.):

DELIMITER //
CREATE TRIGGER trg_auto_assign_tech
BEFORE INSERT ON 案例表
FOR EACH ROW
BEGIN
    DECLARE total_techs INT;
    DECLARE next_case_rank INT;
    DECLARE target_tech_rank INT;

    -- Get total number of technicians (handles dynamic changes if you add/remove techs later)
    SELECT COUNT(*) INTO total_techs FROM Technicians;

    -- Calculate the rank of the new case (total existing cases + 1)
    SELECT COALESCE(MAX(ID_CASE), 0) + 1 INTO next_case_rank FROM 案例表;
    -- If ID_CASE is an auto-increment field, you can use NEW.ID_CASE instead for accuracy

    -- Find which tech rank the new case should go to
    SET target_tech_rank = MOD(next_case_rank - 1, total_techs) + 1;

    -- Assign the corresponding technician ID to the new case
    SET NEW.ID_TEC_ASSIGNED = (
        SELECT ID_TEC FROM (
            SELECT ID_TEC, ROW_NUMBER() OVER (ORDER BY ID_TEC) AS TechRank
            FROM Technicians
        ) ranked_techs
        WHERE ranked_techs.TechRank = target_tech_rank
    );
END //
DELIMITER ;

How This Works: #

Every time a new case is inserted, the trigger calculates its position in the sequence, maps it to a technician via cyclic logic, and automatically sets the ID_TEC_ASSIGNED field. It even adapts if you add or remove technicians later (since it uses COUNT(*) to get the current total).

Fairness Check #

With 100 cases and 5 technicians, each tech will get exactly 20 cases (100/5)—perfectly fair. For any number of cases, the modulo logic ensures the distribution is as even as possible (if cases aren't a multiple of tech count, some techs get one extra case, which is the fairest possible split).

内容的提问来源于stack exchange，提问作者Arnell Vasquez C