PostgreSQL多表查询任务剩余时间结果异常，求解决

Hey there! Let's fix that query to get the accurate remaining hours for each task. The core issue with your previous attempts was handling NULL values properly and avoiding the Cartesian product that happens when joining multiple tables with multiple matching records.

Why Your Previous Queries Failed #

Let's break down what went wrong:

• First attempt: Directly joining tsk with hr and inte creates a Cartesian product for tasks that have multiple entries in either table. For tskid=1, you get 2 hr records × 1 inte record = 2 rows, leading to duplicated tskid values and incorrect calculations.
• Second attempt: You aggregated the subqueries, but didn't handle NULL values for tasks that have no matching entries in hr or inte. When inteqty is NULL (like for tskid=2), hrqty + inteqty becomes NULL, and tskhr - NULL also results in NULL—your outer coalesce turns this into 0 instead of the correct 80.
• Third attempt: Joining first then aggregating amplifies the Cartesian product issue. The sum ends up counting the task hours multiple times and miscalculating the subtracted values, leading to wildly wrong numbers.

The Correct Query #

Here's the SQL that will give you the expected results:

SELECT 
    t.tskid,
    COALESCE(t.tskhr - COALESCE(h.total_hr, 0) - COALESCE(i.total_inte, 0), 0) AS hr
FROM tsk t
LEFT JOIN (
    -- Aggregate total hours from hr table per task
    SELECT hrtsk, SUM(hrqty) AS total_hr
    FROM hr
    GROUP BY hrtsk
) h ON t.tskid = h.hrtsk
LEFT JOIN (
    -- Aggregate total hours from inte table per task
    SELECT intetsk, SUM(inteqty) AS total_inte
    FROM inte
    GROUP BY intetsk
) i ON t.tskid = i.intetsk;

How This Works #

1. Pre-aggregate subqueries: We first calculate the total hours for each task in hr and inte separately. This ensures each task has only one row in each subquery, eliminating the Cartesian product problem.
2. Handle NULLs with COALESCE: For tasks that have no entries in hr or inte, COALESCE converts the NULL sum to 0. This way, subtraction works correctly even when there's no matching data (like tskid=2, which has no inte entries—we subtract 0 instead of dealing with NULL).
3. Final calculation: Subtract the total aggregated hours from both tables from the task's total hours. The outer COALESCE is a safety net in case tskhr itself is NULL (though your sample data doesn't have this case).

Expected Output #

Running this query will give you exactly what you need:

+-------+------+
| tskid | hr   |
+-------+------+
| 1     | 28.5 |
| 2     | 80.0 |
+-------+------+

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