超麻烦的问题 查询汇总的问题 急
现在有两张表 数据如
tab1
id shuliang
001 15
001 15
002 22
002 10
003 10
003 2
003 2
006 2
007 20
tab2
id shuliang
001 12
001 12
002 12
002 13
004 45
005 20
想得到如下的结果
id shuliang
001 54
002 57
003 14
004 45
005 20
006 2
007 20
[解决办法]
select id,sum(shuliang) shuliang
from (
select * from tab1
union all
select * from tab2
)a
group by id
[解决办法]
select t.id,sum(t.shuliang) as shuliang from (select * from tab1 union all select * from tab2) t group by t.id
[解决办法]
create table tb1(spid char(3),shuliang int)
create table tb2(spid char(3),shuliang int)
insert into tb1 values( '001 ',15)
insert into tb1 values( '001 ',15)
insert into tb1 values( '002 ',22)
insert into tb1 values( '002 ',10)
insert into tb1 values( '003 ',10)
insert into tb1 values( '003 ',2)
insert into tb1 values( '003 ',2)
insert into tb1 values( '006 ',2)
insert into tb1 values( '007 ',20)
insert into tb2 values( '002 ',12)
insert into tb2 values( '002 ',13)
insert into tb2 values( '001 ',12)
insert into tb2 values( '001 ',12)
insert into tb2 values( '004 ',45)
insert into tb2 values( '005 ',20)
Select
IsNull(A.spid, B.spid) As spid,
IsNull(A.shuliang, 0) + IsNull(B.shuliang, 0) As shuliang
From
(Select spid, SUM(shuliang) As shuliang From tb1 Group By spid) A
Full Join
(Select spid, SUM(shuliang) As shuliang From tb2 Group By spid) B
On A.spid = B.spid
Drop Table tb1, tb2
--Result
/*
spidshuliang
00154
00257
00314
00445
00520
0062
00720
*/
[解决办法]
create table tb1(spid char(3),shuliang int)
create table tb2(spid char(3),shuliang int)
insert into tb1 values( '001 ',15)
insert into tb1 values( '002 ',22)
insert into tb1 values( '015 ',10)
insert into tb1 values( '003 ',2)
insert into tb1 values( '006 ',2)
insert into tb1 values( '007 ',20)
insert into tb2 values( '002 ',12)
insert into tb2 values( '002 ',12)
insert into tb2 values( '001 ',12)
insert into tb2 values( '001 ',12)
insert into tb2 values( '004 ',45)
insert into tb2 values( '005 ',20)
select spid,sum(shuliang) shuliang from
(
select * from tb1
union all
select * from tb2
) t
group by spid
drop table tb1,tb2
/*
spid shuliang
---- -----------
001 39
002 46
003 2
004 45
005 20
006 2
007 20
015 10
（所影响的行数为 8 行）
*/
[解决办法]
create table #tb1(spid char(3),shuliang int)
create table #tb2(spid char(3),shuliang int)
insert into #tb1 values( '001 ',15)
insert into #tb1 values( '002 ',22)
insert into #tb1 values( '015 ',10)
insert into #tb1 values( '003 ',2)
insert into #tb1 values( '006 ',2)
insert into #tb1 values( '007 ',20)
insert into #tb2 values( '002 ',12)
insert into #tb2 values( '002 ',12)
insert into #tb2 values( '001 ',12)
insert into #tb2 values( '001 ',12)
insert into #tb2 values( '004 ',45)
insert into #tb2 values( '005 ',20)
select id , isnull(sum(shuliang),0)
from (select id,isnull(sum(shuliang),0) from #tab1 group by id) as tab1
left join (select id,isnull(sum(shuliang),0) from #tab2 group by id) as tab2
on tab1.id = tab2.id
group by id
