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