使用MySQL更新贡献计算表：按项目统计开发者贡献值

Got it, let's tackle this step by step. You need to compute the contribution field as each developer's share of total hours within their respective project. Here's how to do it cleanly in MySQL:

Key Calculation Logic #

For each project (projected), the contribution value for a developer is:

• (developer's total hours / total hours of the project) * 100 (formatted as a percentage, rounded to 2 decimal places for readability)

MySQL 8.0+ (Using Window Functions - Most Efficient) #

Window functions make this straightforward since they let you calculate project totals without messy nested subqueries:

UPDATE Contribution c
JOIN (
    SELECT 
        projected,
        `developer id` AS developer_id, -- Backticks required because column name has a space
        total_hours,
        ROUND((total_hours / SUM(total_hours) OVER (PARTITION BY projected)) * 100, 2) AS calculated_contribution
    FROM Contribution
) calc 
    ON c.projected = calc.projected 
    AND c.`developer id` = calc.developer_id
SET c.contribution = calc.calculated_contribution;

Quick Breakdown: #

• SUM(total_hours) OVER (PARTITION BY projected) calculates the total hours for each project group on the fly
• We compute the percentage share, round it to 2 decimals, then join back to the original table to update the contribution field
• Don't forget the backticks around developer id—MySQL throws errors if column names with spaces aren't escaped properly

MySQL 5.x Alternative (No Window Functions) #

If you're stuck on an older MySQL version that doesn't support window functions, use nested subqueries to first compute project totals:

UPDATE Contribution c
JOIN (
    SELECT 
        c1.projected,
        c1.`developer id` AS developer_id,
        ROUND((c1.total_hours / c2.project_total) * 100, 2) AS calculated_contribution
    FROM Contribution c1
    JOIN (
        SELECT projected, SUM(total_hours) AS project_total
        FROM Contribution
        GROUP BY projected
    ) c2 ON c1.projected = c2.projected
) calc 
    ON c.projected = calc.projected 
    AND c.`developer id` = calc.developer_id
SET c.contribution = calc.calculated_contribution;

Verify the Results #

After running the update, double-check the output with this query to confirm values are correct:

SELECT * FROM Contribution ORDER BY projected, `developer id`;

Example Output (Matching Your Sample Data): #

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