我有三张桌子:面积,人,余额明细
面积:
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 循环中不会有查询
冉冉说
慕莱坞森
长风秋雁