使用垂直Join重构Table A为Table B的SQL重复结果问题排查

First, let's break down why your original query is producing duplicate results:

• You're only joining on the id column, but not including gd (gender) in the join conditions. Since each id has two gd values (M and F), this creates a cross-match between rows of different genders, leading to duplicates.
• Your final LEFT JOIN uses va_15.id = va_10.id as the condition, which doesn't account for gd either—this should link back to the base va_5 subquery's id and gd instead.

Solution 1: Corrected Join Approach #

We'll fix the join conditions to match both id and gd, and use COALESCE to handle any potential NULL values if a group has no data for a specific range:

SELECT
    va_5.id,
    va_5.gd,
    va_5.va5,
    va_10.va10,
    va_15.va15,
    COALESCE(va_5.va5, 0) + COALESCE(va_10.va10, 0) + COALESCE(va_15.va15, 0) AS vatotal
FROM (SELECT id, gd, value AS va5 FROM tableA WHERE gf = 1 AND gt = 5) AS va_5
LEFT JOIN (SELECT id, gd, value AS va10 FROM tableA WHERE gf = 6 AND gt = 10) AS va_10
    ON va_5.id = va_10.id AND va_5.gd = va_10.gd
LEFT JOIN (SELECT id, gd, value AS va15 FROM tableA WHERE gf = 11 AND gt = 15) AS va_15
    ON va_5.id = va_15.id AND va_5.gd = va_15.gd

Solution 2: More Efficient Conditional Aggregation (Recommended) #

Instead of joining multiple subqueries (which scans the table multiple times), we can use conditional aggregation to pivot the rows into columns in a single pass over the table. This is cleaner and more performant:

SELECT
    id,
    gd,
    MAX(CASE WHEN gf = 1 AND gt = 5 THEN value ELSE 0 END) AS va5,
    MAX(CASE WHEN gf = 6 AND gt = 10 THEN value ELSE 0 END) AS va10,
    MAX(CASE WHEN gf = 11 AND gt = 15 THEN value ELSE 0 END) AS va15,
    SUM(value) AS vatotal
FROM tableA
GROUP BY id, gd
ORDER BY id, gd;

How this works:

• The CASE WHEN clauses filter values for each range (1-5, 6-10, 11-15) and map them to the corresponding columns. We use MAX here because each id + gd + range combination has exactly one row—SUM would work too.
• SUM(value) directly calculates the total for each id + gd group, avoiding the need to manually add the three column values.
• GROUP BY id, gd aggregates all rows for the same id and gender into a single row, which matches the structure of Table B perfectly.

Both solutions will return your desired Table B output without duplicate rows. The conditional aggregation method is preferred for most scenarios due to its efficiency and simplicity.

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