mysql sum求id的和,具有相同ID的MySQL SUM

抱歉,我只学习了PHP& MySQL,我已经使用谷歌搜索了一个多星期,但没有找到任何答案.

我创建了一个简单的财务脚本,该表如下所示：

table_a

aid | value

1 | 100

2 | 50

3 | 150

table_b

bid | aid | value

1 | 1 | 10

2 | 1 | 15

3 | 2 | 5

4 | 2 | 10

5 | 3 | 25

6 | 3 | 40

我想要这样的结果

No | ID | Total | Balance

1 | 1 | 10 | 90

2 | 1 | 25 | 75

3 | 2 | 5 | 45

4 | 2 | 15 | 35

5 | 3 | 25 | 125

6 | 3 | 65 | 85

有人可以帮助我解决我的问题吗？

谢谢

解决方法:

SELECT

tb.bid as No,

ta.aid as ID,

tb.value as Total,

ta.value-tb.total as Balance

FROM

table_a AS ta

INNER JOIN (

SELECT

tbx.aid AS aid,

tbx.bid AS bid,

tbx.value AS value,

SUM(tby.value) AS total

FROM

table_b AS tbx

INNER JOIN table_b AS tby ON tby.aid=tbx.aid AND tby.bid<=tbx.bid

GROUP BY tbx.bid

ORDER BY tbx.bid

) AS tb ON tb.aid=ta.aid

ORDER BY tb.bid

正如@Quassnoi指出的那样,这对于MySQL来说不是很有效.我尝试使用怪胎连接而不是子查询,因为内部查询本身就可以使用.

编辑

对此引起了一些兴趣,发现连接版本的速度是@Quassnoi的子查询版本的两倍……有人知道为什么会这样吗？

编辑

回答第二个问题(在下面的评论中)：

SELECT

table_a.aid AS aid,

SUM(table_b.value) AS Total,

table_a.value-SUM(table_b.value) AS Balance

FROM

table_a

INNER JOIN table_b ON table_a.aid=table_b.aid

GROUP BY table_a.aid

标签：sql,mysql

来源： https://codeday.me/bug/20191201/2079423.html