如何在SQL中按Location分组、UNum关联多表统计COUNT与SUM

如何通过按Location分组、以UNum为关联条件，从以下四个SQL表中统计各表的PLAN计数和BALANCE总和，生成指定格式的结果集？

表结构及数据 #

TABLE 1 #

NAME               PLAN             OPEN           LOCATION        BALANCE          UNum
John               A                3/12/2023      27              999.99           11
Michelle           A                3/17/2023      13              1200.00          7
James              B                3/18/2023      9               4500.00          23
Juan               D                3/11/2023      9               234.20           37
Michelle           B                3/16/2023      13              594.00           7

Table 2 #

NAME               PLAN              OPEN           LOCATION        BALANCE         UNum
Michelle           H                 3/09/2023      13              1549.00         7
Michelle           J                 3/11/2023      13              749.00          7
Juan               J                 3/13/2023      9               1700.00         37

Table 3 #

NAME                PLAN              OPEN           LOCATION        BALANCE        UNum
Michelle            M                 3/18/2023      13              498.02         7
John                P                 3/20/2023      27              9800.00        11

TABLE 4 #

NAME                   LOCATION         UNum
JOHN                   27               11
Michelle               13               7
James                  9                23
Juan                   9                37

期望结果格式 #

NAME              LOCATION      TABLE1PLANCOUNT    TABLE1PLANBALANCE   TABLE2PLANCOUNT   TABLE2PLANBALANCE   TABLE3PLANCOUNT    TABLE3PLANBALANCE
John              27             1                 999.99              0                 0                   1                  9800.00
Michelle          13             2                 1794.00             2                 2298.00             1                  498.02
James             9              1                 4500.00             0                 0                   0                  0
Juan              9              1                 234.00              1                 1700.00             0                  0

以TABLE4作为主表（包含所有目标用户的基础信息），通过LEFT JOIN关联三个业务表的统计子查询，即可生成符合要求的结果集：

完整SQL语句 #

SELECT
    t4.NAME,
    t4.LOCATION,
    COALESCE(t1.PLAN_COUNT, 0) AS TABLE1PLANCOUNT,
    COALESCE(t1.BALANCE_SUM, 0) AS TABLE1PLANBALANCE,
    COALESCE(t2.PLAN_COUNT, 0) AS TABLE2PLANCOUNT,
    COALESCE(t2.BALANCE_SUM, 0) AS TABLE2PLANBALANCE,
    COALESCE(t3.PLAN_COUNT, 0) AS TABLE3PLANCOUNT,
    COALESCE(t3.BALANCE_SUM, 0) AS TABLE3PLANBALANCE
FROM TABLE4 t4
LEFT JOIN (
    SELECT UNum, COUNT(PLAN) AS PLAN_COUNT, SUM(BALANCE) AS BALANCE_SUM
    FROM TABLE1
    GROUP BY UNum
) t1 ON t4.UNum = t1.UNum
LEFT JOIN (
    SELECT UNum, COUNT(PLAN) AS PLAN_COUNT, SUM(BALANCE) AS BALANCE_SUM
    FROM TABLE2
    GROUP BY UNum
) t2 ON t4.UNum = t2.UNum
LEFT JOIN (
    SELECT UNum, COUNT(PLAN) AS PLAN_COUNT, SUM(BALANCE) AS BALANCE_SUM
    FROM TABLE3
    GROUP BY UNum
) t3 ON t4.UNum = t3.UNum
ORDER BY t4.LOCATION, t4.NAME;

逻辑说明 #

1. 主表锚定：用TABLE4作为结果骨架，确保所有需要展示的用户都能被包含，不会遗漏。
2. 子查询统计：对TABLE1、TABLE2、TABLE3分别按UNum分组，计算每个UNum对应的PLAN数量和BALANCE总和。
3. 关联补零：通过LEFT JOIN关联主表与统计子查询，用COALESCE将无数据的NULL值替换为0，匹配期望格式。
4. 排序规整：按LOCATION和NAME排序，让结果更易读。

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