如何对 3 个表中的每个项目求和

Question

我有三张桌子：面积，人，余额明细面积：Code  AreaName0001  A 0002  B0003  C0004  D人：id code personid personname customername customernumber1  0001 1000     test1      loop         50002  0001 1000     test1      loop         70003  0002 1001     test2      loop2        60004  0003 1002     test3      loop3        60055  0001 1000     test1      loop5        6100余额详情 ：id period       customernumber balance1  2019-12-31   5000           502  2019-12-31   6000           1503  2019-12-31   6005           1404  2019-12-31   6100           405  2019-12-31   7000           206  2020-01-17   5000           1507  2020-01-17   6000           408  2020-01-24   6100           1809  2020-01-24   6000           170我想根据每个项目的区号和时间段获得余额。我使用了以下查询$query= $con->query("SELECT * FROM Area WHERE Code NOT IN ('0004') GROUP BY AreaName");while ($row = $query->fetch_array()) {     $balancequery = $con->query("SELECT SUM(BalanceDetail.balance) as balance FROM Person JOIN BalanceDetail ON Person.customernumber= BalanceDetail.customernumber WHERE Person.code= '$row[Code]' AND period='2019-12-31'");     $balancequery2 = $con->query("SELECT SUM(BalanceDetail.balance) as balance FROM Person JOIN BalanceDetail ON Person.customernumber= BalanceDetail.customernumber WHERE Person.code= '$row[Code]' AND period='2020-01-17'");     $balancequery3 = $con->query("SELECT SUM(BalanceDetail.balance) as balance FROM Person JOIN BalanceDetail ON Person.customernumber= BalanceDetail.customernumber WHERE Person.code= '$row[Code]' AND period='2020-01-24'");}我想把它组合成一个sql，所以在 while 循环中不会有查询

Answer 1

您可以使用条件聚合来解决此问题，只需对每个周期的相关值求和：

SELECT a.code,

       SUM(CASE WHEN period='2019-12-31' THEN b.balance ELSE 0 END) AS `balance 2019-12-31`,

       SUM(CASE WHEN period='2020-01-17' THEN b.balance ELSE 0 END) AS `balance 2020-01-17`,

       SUM(CASE WHEN period='2020-01-24' THEN b.balance ELSE 0 END) AS `balance 2020-01-24`

FROM Area a

JOIN Person p ON p.code = A.code

JOIN BalanceDetail b ON b.customernumber = p.customernumber 

GROUP BY a.code

输出：

code    balance 2019-12-31  balance 2020-01-17  balance 2020-01-24

1       110                 150                 180

2       150                 40                  170

3       140                 0                   0

Answer 2

您可以使用分组依据

SELECT Person.Code, BalanceDetail.period, SUM(BalanceDetail.balance) as balance 

FROM Person JOIN BalanceDetail 

ON Person.customernumber= BalanceDetail.customernumber

WHERE Code IN (SELECT Code  FROM Area WHERE Code NOT IN ('0004'))

group by Person.Code, BalanceDetail.period

Answer 3

SELECT  bd.period,

        a.AreaName,

        SUM(bd.balance) as balance

    FROM Person AS p

    JOIN BalanceDetail AS bd ON p.customernumber = bd.customernumber

    JOIN Area AS a  ON a.code = p.code

    GROUP BY bd.period, p.code;