我有2张桌子:
tbl1 表
user_id amount
1 100
1 200
tbl2 表
user_id amount
1 900
1 800
我需要从这个表中为某些 user_id 获取“amount”列的总和。
我可以用 2 个查询来做到这一点:
SELECT SUM(amount) AS sum1 FROM tbl1 WHERE user_id = 1
SELECT SUM(amount) AS sum2 FROM tbl2 WHERE user_id = 1
我怎样才能在 1 个查询中做到这一点?
输出应该是:
sum1 sum2
300 1700
这是一种方法:
SELECT (SELECT SUM(amount) AS sum1 FROM tbl1 WHERE user_id = 1) as amount1,
(SELECT SUM(amount) AS sum2 FROM tbl2 WHERE user_id = 1) as amount2;
如果您想为多个用户有效地执行此操作,您可以执行以下操作:
SELECT (SELECT SUM(amount) FROM tbl1 t WHERE t.user_id = u.user_id) as amount1,
(SELECT SUM(amount) FROM tbl2 t WHERE t.user_id = u.user_id) as amount2
FROM (SELECT 1 as user_id UNION ALL
SELECT 2 as user_id
) u
如果您在tbl1(user_id, amount)
和上有索引,这将是有效的tbl2(user_id, amount)
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句