获取角色已分配人数并限制最低分配数量的数据库方案

核心逻辑说明 #

要实现“岗位角色需求数量不得低于该角色已分配人数”的规则，核心是按「任务-角色」组合统计已分配的唯一自由职业者数量——即使同一自由职业者被分配到该任务该角色的多个日期，也仅算作1个已分配人数，以此值作为需求输入框的min属性上限。

1. 已分配人数统计查询 #

利用左关联查询，确保即使某角色无分配记录，也能返回0作为统计值，适配需求可设为0的场景。以MySQL为例：

SELECT 
    rr.task_id,
    rr.role_id,
    COALESCE(COUNT(DISTINCT a.freelancer_id), 0) AS assigned_count
FROM 
    role_requirements rr
LEFT JOIN 
    assignments a 
    ON rr.task_id = a.task_id 
    AND rr.role_id = a.role_id
GROUP BY 
    rr.task_id, rr.role_id;

• LEFT JOIN：保留所有岗位角色需求记录，包括未分配的项
• COUNT(DISTINCT a.freelancer_id)：统计该任务该角色下的唯一自由职业者数量
• COALESCE(..., 0)：将无分配记录时的NULL转换为0

2. 前端min属性绑定 #

在编辑岗位角色需求的页面，将上述查询返回的assigned_count直接赋值给<input type="number">的min属性：

<!-- 示例：假设从后端获取的assigned_count为1 -->
<input type="number" name="required_quantity" min="1" value="2">

这样输入框会自动限制用户无法输入小于已分配人数的数值。

3. 数据库层面的约束强化 #

仅前端限制存在被绕过的风险，需在数据库层面添加约束，确保数据一致性：

方案A：CHECK约束（适用于MySQL 8.0.16+、PostgreSQL等支持CHECK的数据库） #

ALTER TABLE role_requirements
ADD CONSTRAINT chk_requirement_not_less_than_assigned
CHECK (required_quantity >= (
    SELECT COALESCE(COUNT(DISTINCT freelancer_id), 0)
    FROM assignments a
    WHERE a.task_id = role_requirements.task_id 
      AND a.role_id = role_requirements.role_id
));

方案B：触发器（适用于不支持CHECK的旧版本数据库） #

创建BEFORE INSERT和BEFORE UPDATE触发器，在数据写入/修改前校验规则：

-- 插入前校验
DELIMITER //
CREATE TRIGGER trg_requirement_min_insert
BEFORE INSERT ON role_requirements
FOR EACH ROW
BEGIN
    DECLARE assigned_total INT;
    SELECT COALESCE(COUNT(DISTINCT freelancer_id), 0) INTO assigned_total
    FROM assignments
    WHERE task_id = NEW.task_id AND role_id = NEW.role_id;
    
    IF NEW.required_quantity < assigned_total THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '需求数量不能低于已分配人数';
    END IF;
END //
DELIMITER ;

-- 更新前校验
DELIMITER //
CREATE TRIGGER trg_requirement_min_update
BEFORE UPDATE ON role_requirements
FOR EACH ROW
BEGIN
    DECLARE assigned_total INT;
    SELECT COALESCE(COUNT(DISTINCT freelancer_id), 0) INTO assigned_total
    FROM assignments
    WHERE task_id = NEW.task_id AND role_id = NEW.role_id;
    
    IF NEW.required_quantity < assigned_total THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '需求数量不能低于已分配人数';
    END IF;
END //
DELIMITER ;

适配多日任务场景 #

针对多日任务中“部分角色无需全周期配置”的情况，只要assignments表记录了该自由职业者与任务-角色的关联（无论单天还是多天），COUNT(DISTINCT)都会正确统计唯一人数，无需额外调整逻辑。

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