mysql - 我们如何获得两个表上的值的总和？

Question

我有两张 table

table: a---------------------id    amount  status---------------------4031  3000    14032  4000    14033  5000    14034  6000    14035  7000    14036  8000    0

table: s--------------id a_id  b_id --------------1  4031  202  4031  213  4032  234  4032  245  4033  256  4033  267  4034  218  4034  209  4035  2510 4035  2911 4036  2112 4036  20

我们如何获得 a.amount 的总和，其中 has ( b_id = 20 AND b_id = 21) AND a.status = 1？

答案应该是 9000。

Answer 1

SELECT SUM(amount) FROM (
JOIN s ON a.id = s.id
WHERE STATUS =1
AND (b_id = 20 OR b_id = 21) GROUP BY a.id
) AS amounts

总计:9000

如果您可以添加相同数量的多次，我想这应该可以在没有 join 的情况下工作:

SELECT SUM(amount) AS total 
FROM `a`, `s` 
WHERE a_id = a.id AND (b_id = 20 OR b_id = 21) AND status = 1

总计:18000