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

我有三张桌子:面积,人,余额明细


面积:


Code  AreaName

0001  A 

0002  B

0003  C

0004  D

人:


id code personid personname customername customernumber

1  0001 1000     test1      loop         5000

2  0001 1000     test1      loop         7000

3  0002 1001     test2      loop2        6000

4  0003 1002     test3      loop3        6005

5  0001 1000     test1      loop5        6100

余额详情 :


id period       customernumber balance

1  2019-12-31   5000           50

2  2019-12-31   6000           150

3  2019-12-31   6005           140

4  2019-12-31   6100           40

5  2019-12-31   7000           20

6  2020-01-17   5000           150

7  2020-01-17   6000           40

8  2020-01-24   6100           180

9  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 循环中不会有查询


侃侃尔雅
浏览 112回答 3
3回答

冉冉说

您可以使用条件聚合来解决此问题,只需对每个周期的相关值求和: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 aJOIN Person p ON p.code = A.codeJOIN BalanceDetail b ON b.customernumber = p.customernumber GROUP BY a.code输出:code    balance 2019-12-31  balance 2020-01-17  balance 2020-01-241       110                 150                 1802       150                 40                  1703       140                 0                   0

慕莱坞森

您可以使用分组依据SELECT Person.Code, BalanceDetail.period, SUM(BalanceDetail.balance) as balance FROM Person JOIN BalanceDetail ON Person.customernumber= BalanceDetail.customernumberWHERE Code IN (SELECT Code  FROM Area WHERE Code NOT IN ('0004'))group by Person.Code, BalanceDetail.period

长风秋雁

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;
打开App,查看更多内容
随时随地看视频慕课网APP