Question

SQL server中如何将两个查询结果集做运算

比如：得到各个机构的销售数量及对应的机构名称
select count as count1, branch from table1 group by branch
select count as count2, branch from table2 group by branch
相同机构的数量做运算，怎么实现

Answer 1

select isnull(t1.branch,t2.branch), isnull(t1.count1,0)+isnull(t2.count2,0) as 'countSum'
from (select sum(count) as count1, branch from table1 group by branch) t1 full outer join
(select sum(count) as count2, branch from table2 group by branch)t2 on t1.branch =t2.branch
这个会把所有的,相同的不相同的机构都列出来.
如果只要相同的,一个内连接就行了:
select t1.branch, isnull(t1.count1,0)+isnull(t2.count2,0) as 'countSum'
from (select sum(count) as count1, branch from table1 group by branch) t1 inner join
(select sum(count) as count2, branch from table2 group by branch)t2 on t1.branch =t2.branch

Answer 2

将两条查询语句作为子查询的一部分连表.
select t1.count1+t2.count2 as 'countSum',t1.branch
(select count as count1, branch from table1 group by branch)t1
(select count as count2, branch from table2 group by branch)t2 on t1.branch =t2.branch

这种写法有一个潜在的限制,t1中的branch 必须包含t2,所以你的需求一般这么写:
select sum(counts)counts,branch
from(select * from(select count as counts, branch from table1 group by branch)t1
union
select * from(select count as counts, branch from table2 group by branch )t1)t
group by branch