一个简单的查询问题
create table o (
bh varchar(10),
mc varchar(10),
je int)
create table t (
bh varchar(10),
mc varchar(10),
je int)
insert o
select '01 ', 'A ', 20
union all
select '02 ', 'B ', 30
union all
select '04 ', 'D ', 60
insert t
select '02 ', 'B ', 50
union all
select '03 ', 'C ', 10
union all
select '04 ', 'D ', 100
select allbh,allmc,ls,jin,chajia from (
select isnull(o.bh,t.bh) as allbh,
isnull(o.mc,t.mc) allmc,
isnull(o.je,0) as ls,
isnull(t.je,0) as jin,
(isnull(t.je,0)-isnull(o.je,0)) as chajia
from o full join t on o.bh=t.bh
) as b order by b.allbh
select allbh= '合计 ',allmc= ' ',ls=sum(ls),jin=sum(jin),chajia=sum(chajia) from
(select isnull(o.bh,t.bh) as allbh,
isnull(o.mc,t.mc) allmc,
isnull(o.je,0) ls,
isnull(t.je,0) jin,
(isnull(t.je,0)-isnull(o.je,0)) as chajia
from o full join t on o.bh=t.bh) as c
以上两个查询语句会形成两个结果
如何把两个查询结果组合在一起
[解决办法]
select allbh,allmc,ls,jin,chajia from (
select isnull(o.bh,t.bh) as allbh,
isnull(o.mc,t.mc) allmc,
isnull(o.je,0) as ls,
isnull(t.je,0) as jin,
(isnull(t.je,0)-isnull(o.je,0)) as chajia
from o full join t on o.bh=t.bh
) as b
union all
select allbh= '合计 ',allmc= ' ',ls=sum(ls),jin=sum(jin),chajia=sum(chajia) from
(select isnull(o.bh,t.bh) as allbh,
isnull(o.mc,t.mc) allmc,
isnull(o.je,0) ls,
isnull(t.je,0) jin,
(isnull(t.je,0)-isnull(o.je,0)) as chajia
from o full join t on o.bh=t.bh) as c
order by b.allbh
[解决办法]
用union all
