多个 INNER JOIN 上的 SQL SUM

Question

我正在尝试使用连接通过多个表获取特定字段的总和。当我尝试将这些值放在一起时，我得到的 SUM 是错误的。

我有表状态为

| STATE |   MONTH | ID | TOTAL |
--------------------------------
|    FL |    July |  1 | 10000 |
|    CA |   April | 32 |  2000 |

我有另一个表余额

| STATE |  Balance|
-------------------
|    FL |      100|
|    FL |      200|
|    CA |      300|
|    CA |      200|
|    CA |      100|

我还有一张 table 贷款作为

| STATE |     Loan|
-------------------
|    FL |      500|
|    FL |      600|
|    CA |      700|
|    CA |      100|
|    CA |      200|

我的查询结果是，

| STATE |     Loan| Balance|
----------------------------
|    FL |     1100|     300|
|    CA |     1000|     600|

当我尝试使用以下查询时，我正确地获得了状态贷款的总和，

SELECT 
  S.STATE, 
  SUM(L.Loan) 
FROM State AS S
  INNER JOIN Loan AS L ON L.STATE = S.STATE
GROUP BY
 S.STATE

我得到以下结果，

| STATE |      Loan|
--------------------
|    FL |      1100|
|    CA |      1000|

同样，我可以从余额表中得到总和。
我想要实现的是使用单个事务从两个表中获取总和。

虽然，我能够获得所需的值，如果我做这样的事情，

SELECT
  STATE AS STATE
  SUM(DataSetB.Balance) AS Balance
FROM
    (
    SELECT 
      STATE AS STATE,
      B.Balance AS Balance
    FROM
        (
        SELECT 
          S.STATE AS STATE, 
          SUM(L.Loan) AS Loan,
        FROM State AS S
          INNER JOIN Loan AS L ON L.STATE = S.STATE
        GROUP BY S.STATE
        )
        AS DataSetL
     INNER JOIN Balance AS B ON B.STATE = DataSetL.STATE
     GROUP BY
       DataSetL.STATE, B.Balance
     ) AS DataSetB
GROUP BY 
  DataSetB.STATE

然而，这在我拥有的大型数据集上并不是很可行。
我试过，

SELECT 
  S.STATE AS STATE, 
  SUM(L.Loan) AS Loan,
  SUM(B.Balance) AS Balance
FROM State AS S
  INNER JOIN Loan AS L ON L.STATE = S.STATE
  INNER JOIN Balance AS B ON B.STATE = S.STATE
GROUP BY 
  S.STATE

但这给了我作为实际值(value)倍数的值(value)。它实际上是子表中存在的行数的倍数。

我不是在要求一个确切的解决方案，但任何类型的指针都会很好。

Answer 1

你可以试试这个，用 UNION ALL联合 Balance和 Loan表和使用一点技巧，

Balance填写 Loan列集 0

Loan填写 Balance列集 0

然后 JOIN与 State做 SUM

create table State(
  STATE varchar(50)
);



insert into State values ('FL'); 
insert into State values ('CA'); 
create table Balance(
  STATE varchar(50),
  Balance int
);



insert into Balance values ('FL',100);
insert into Balance values ('FL',200);
insert into Balance values ('CA',300);
insert into Balance values ('CA',200);
insert into Balance values ('CA',100);

create table Loan(
  STATE varchar(50),
  loan int
);

insert into loan values ( 'FL',500);
insert into loan values ( 'FL',600);
insert into loan values ( 'CA',700);
insert into loan values ( 'CA',100);
insert into loan values ( 'CA',200);

查询 1 :

SELECT s.STATE,SUM(t1.Loan) as 'Loan',SUM(t1.Balance) as 'Balance'
FROM 
(
  SELECT STATE,0 AS Loan,Balance  
  FROM Balance 
  UNION ALL
  SELECT STATE,Loan,0   
  FROM Loan 
) t1
INNER JOIN State s on s.STATE = t1.STATE
GROUP BY s.STATE

Results :

| STATE | Loan | Balance |
|-------|------|---------|
|    CA | 1000 |     600 |
|    FL | 1100 |     300 |