代码之家 › 专栏 › 技术社区 › Si8

如何在SQL中从多个表中获取列值的COUNT和SUM

join sql

Si8 · 技术社区 · 1 年前

表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

表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

表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

表4

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

如何按位置生成此组并按UNum加入:

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

1 回复 | 直到 1 年前

nbk 1 年前

你会发现一个共同点,这里可以命名为位置。

我选择位置,但如果一个名称可以有多个位置,那么将其更改为名称并不困难

SELECT
  t4.NAME, t4.LOCATION 
   , COALESCE(TABLE1PLANCOUNT,0) TABLE1PLANCOUNT ,COALESCE(TABLE1PLANBALANCE,0) TABLE1PLANBALANCE   
   , COALESCE(TABLE2PLANCOUNT,0) TABLE2PLANCOUNT,COALESCE(TABLE2PLANBALANCE,0)  TABLE2PLANBALANCE  
   , COALESCE(TABLE3PLANCOUNT,0) TABLE3PLANCOUNT,COALESCE(TABLE3PLANBALANCE,0) TABLE3PLANBALANCE
FROM 
Table4 t4 
LEFT JOIN ( SELECT LOCATION , COUNT(*) TABLE1PLANCOUNT , SUM(BALANCE) TABLE1PLANBALANCE    FROM Table1 GROUP BY LOCATION) t1 ON t4.LOCATION = t1.LOCATION 
LEFT JOIN ( SELECT LOCATION , COUNT(*) TABLE2PLANCOUNT, SUM(BALANCE) TABLE2PLANBALANCE  FROM Table2 GROUP BY LOCATION) t2 ON t4.LOCATION = t2.LOCATION 
LEFT JOIN ( SELECT LOCATION , COUNT(*) TABLE3PLANCOUNT    , SUM(BALANCE) TABLE3PLANBALANCE FROM Table3 GROUP BY LOCATION ) t3 ON t4.LOCATION = t3.LOCATION