我有三張桌子：面積，人，余額明細(xì)面積：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我想根據(jù)每個(gè)項(xiàng)目的區(qū)號(hào)和時(shí)間段獲得余額。我使用了以下查詢$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'");}我想把它組合成一個(gè)sql，所以在 while 循環(huán)中不會(huì)有查詢
3 回答
冉冉說(shuō)
TA貢獻(xiàn)1877條經(jīng)驗(yàn) 獲得超1個(gè)贊
您可以使用條件聚合來(lái)解決此問(wèn)題，只需對(duì)每個(gè)周期的相關(guān)值求和：
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
慕萊塢森
TA貢獻(xiàn)1810條經(jīng)驗(yàn) 獲得超4個(gè)贊
您可以使用分組依據(jù)
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
長(zhǎng)風(fēng)秋雁
TA貢獻(xiàn)1757條經(jīng)驗(yàn) 獲得超7個(gè)贊
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;
- 3 回答
- 0 關(guān)注
- 136 瀏覽
添加回答
舉報(bào)
0/150
提交
取消