如何基于任务占比计算特定目标及总目标合计（SQL实现方法）

Got it, let's walk through how to solve this. First, I'll assume a typical table structure for your goal-tracking data (since you didn't share your schema). If your tables are named differently or have extra fields, just tweak the queries to match your setup.

Assumed Table Structure #

I'll use two tables to keep things organized:

1. specific_goals: Stores each specific goal's weight relative to the main 100% goal
2. tasks: Stores each task's percentage relative to its parent specific goal

specific_goals Table #

tasks Table #

Step 1: Calculate Individual Task Contributions to the Main Goal #

Each task's actual impact on the main goal is the product of its percentage of the specific goal and the specific goal's percentage of the main goal. This query breaks that down:

SELECT
    sg.specific_goal_name,
    t.task_name,
    -- Compute the task's % of the overall main goal
    ROUND((t.task_percent_of_goal / 100.0) * (sg.total_weight / 100.0) * 100, 2) AS task_main_goal_percent
FROM
    specific_goals sg
JOIN
    tasks t ON sg.specific_goal_id = t.specific_goal_id;

This will return rows like:

Step 2: Calculate Total for Each Specific Goal #

To get the total percentage each specific goal contributes to the main goal, aggregate the task contributions:

SELECT
    sg.specific_goal_name,
    sg.total_weight AS expected_total,
    ROUND(SUM((t.task_percent_of_goal / 100.0) * (sg.total_weight / 100.0) * 100), 2) AS calculated_total
FROM
    specific_goals sg
JOIN
    tasks t ON sg.specific_goal_id = t.specific_goal_id
GROUP BY
    sg.specific_goal_name, sg.total_weight;

This will show you if your task percentages add up to the expected weight for each specific goal (e.g., Specific Goal 1's calculated total should be 40.00%).

Step 3: Calculate the Main Goal Total #

To get the overall main goal total (which should equal 100% if your data is consistent), use ROLLUP to include a grand total row:

SELECT
    COALESCE(sg.specific_goal_name, 'Main Goal') AS goal_name,
    ROUND(SUM((t.task_percent_of_goal / 100.0) * (sg.total_weight / 100.0) * 100), 2) AS total_percent
FROM
    specific_goals sg
JOIN
    tasks t ON sg.specific_goal_id = t.specific_goal_id
GROUP BY
    ROLLUP(sg.specific_goal_name);

The result will look like this:

Note for Single-Table Setups #

If all your data is in one table (e.g., goal_tasks with columns specific_goal_name, total_weight, task_name, task_percent_of_goal), just remove the JOIN and reference the single table in all queries—no other changes needed.

内容的提问来源于stack exchange，提问作者D. Jhon