resource-platform/sql/migration_cost_allocation.sql

-- 费用分摊: 给 project_resource 增加 allocation_percent(费用分摊占比) 列
-- 并把存量"一个资源关联多个项目"的记录归一为均分(避免占比合计>100导致统计重复计入)
ALTER TABLE `project_resource`
  ADD COLUMN `allocation_percent` DECIMAL(7,4) NOT NULL DEFAULT 100.0000
  COMMENT '费用分摊占比(0~100, 同一资源各关联项目占比合计应为100)';

-- 存量多关联资源: 均分(每个关联占比 = 100 / 关联数)
UPDATE `project_resource` pr
JOIN (
  SELECT resource_id, COUNT(*) AS cnt
  FROM `project_resource`
  GROUP BY resource_id
  HAVING cnt > 1
) s ON s.resource_id = pr.resource_id
SET pr.allocation_percent = ROUND(100.0 / s.cnt, 4);