SQL,如何从两个不同的查询中总结两个总和
我想在两种不同的条件下获取同一个变量的总和。只是想知道是否有办法将这些加在一起。查询的细节并不太相关。基本上只是试图拿第一笔款项,并添加第二笔款项。SQL,如何从两个不同的查询中总结两个总和
例如 -
首先查询:
select sum(amt_tot)
from agbgift, aprdclb
where agbgift_id = '1' and agbgift_id = aprdclb_id
第二个查询:
select sum(amt_tot)
from agbgift, aprxref, aprdclb where
aprxref_id = '1' and
agbgift_id = aprxref_xref_id and
aprxref_id = aprdclb_id and
xref_code in ('SPS','BUS','ORG','OWN','FDN');
最终的结果我找的是 '第一个查询点心' +“第二个查询总和'
使用子句可以轻松实现,如果使用不起作用,可以在from子句中使用别名
with b ( select sum(amt_tot) as bsum
from agbgift, aprxref, aprdclb where
aprxref_id = '1' and
agbgift_id = aprxref_xref_id and
aprxref_id = aprdclb_id and
xref_code in ('SPS','BUS','ORG','OWN','FDN'))
, a (select sum(amt_tot) asum
from agbgift, aprdclb
where agbgift_id = '1' and agbgift_id = aprdclb_id )
select a.asum + b.bsum
from a, b
这是真的在这种情况下:
select a.asum + b.bsum
from (select sum(amt_tot) asum
from agbgift, aprdclb
where agbgift_id = '1' and agbgift_id = aprdclb_id) as a,
( select sum(amt_tot) as bsum
from agbgift, aprxref, aprdclb where
aprxref_id = '1' and
agbgift_id = aprxref_xref_id and
aprxref_id = aprdclb_id and
xref_code in ('SPS','BUS','ORG','OWN','FDN')) as b
谢谢,第一部分非常有用。似乎已经得到了一些结果,只需要用数据库信息进行分析,看看它是否正确! :) – mare630 2014-09-26 17:59:17
我很高兴它工作:-) – user3851404 2014-09-26 19:18:46
在这种情况下,它是这样简单:
SELECT
/* Query 1 (in parentheses) */
(select sum(amt_tot)
from agbgift, aprdclb
where agbgift_id = '1' and agbgift_id = aprdclb_id)
+ /* plus */
/* Query 2 (also in parentheses) */
(select sum(amt_tot)
from agbgift, aprxref, aprdclb where
aprxref_id = '1' and
agbgift_id = aprxref_xref_id and
aprxref_id = aprdclb_id and
xref_code in ('SPS','BUS','ORG','OWN','FDN'))
以更简单的形式: SELECT (SELECT SUM(something)FROM table1 WHERE a = 1)+(SELECT SUM(something)FROM table2 WHERE a = 1) 总计 – 2014-09-26 17:37:28
谢谢,但这是如何更简单?这完全一样。 – GolezTrol 2014-09-26 17:39:47
[子查询(http://dev.mysql.com /doc/refman/5.1/en/subqueries.html)? – 2014-09-26 17:24:55