假设A表字段s1,s2,s3,srow,Amount;B表字段s1,s2,s3,spro,Amount;
我现在要得到一个DataTable,为:s1',s2',s3',SUM(A.Amount),SUM(B.Amount),其中s1',s2',s3'可能为A表中的数据,也可能为B表中的数据,不知道如何才能效率更高呢。
eg:
A: s1 s2 s3 srow Amount
00843 JD005 101 abc 10.00
00843 JD005 101 abcd 20.00
00843 JD005 101 30.00 00843 JE012 101 adc 5.00
00843 JE012 101 abcc 55.00
00843 JE012 101 10.00
B: s1 s2 s3 spro Amount
00843 JD005 101 abdd 15.00
00843 JD005 101 abcd 20.00 00843 JD006 101 adc 5.00
00843 JD006 101 abcc 5.00
00843 JD006 101 1.00的到:DataTable: s1' s2' s3' Amount1 Amount2
00843 JD005 101 60.00 35.00
00843 JE012 101 70.00 0.00
00843 JD006 101 0.00 11.00拜托各位大侠了
我现在要得到一个DataTable,为:s1',s2',s3',SUM(A.Amount),SUM(B.Amount),其中s1',s2',s3'可能为A表中的数据,也可能为B表中的数据,不知道如何才能效率更高呢。
eg:
A: s1 s2 s3 srow Amount
00843 JD005 101 abc 10.00
00843 JD005 101 abcd 20.00
00843 JD005 101 30.00 00843 JE012 101 adc 5.00
00843 JE012 101 abcc 55.00
00843 JE012 101 10.00
B: s1 s2 s3 spro Amount
00843 JD005 101 abdd 15.00
00843 JD005 101 abcd 20.00 00843 JD006 101 adc 5.00
00843 JD006 101 abcc 5.00
00843 JD006 101 1.00的到:DataTable: s1' s2' s3' Amount1 Amount2
00843 JD005 101 60.00 35.00
00843 JE012 101 70.00 0.00
00843 JD006 101 0.00 11.00拜托各位大侠了
SELECT A.s1, A.s2 A.s3, SUM(A.Amount) Amount1,SUM(B.Amount) Amount2
FROM A ,B WHERE A.s1=B.s1 and A.s2=B.s2 and A.s3=B.s3
group by A.s1, A.s2 A.s3请运行。
create table #a( s1 varchar(10),s2 varchar(10),s3 varchar(10),srow varchar(10),Amount numeric(10,2))
insert into #a
select '00843', 'JD005', '101', 'abc', 10.00 union all
select '00843', 'JD005', '101', 'abcd', 20.00 union all
select '00843', 'JD005', '101', '', 30.00 union all select '00843','JE012','101','adc',5.00 union all
select '00843','JE012','101','abcc',55.00 union all
select '00843','JE012','101','',10.00 create table #b( s1 varchar(10),s2 varchar(10),s3 varchar(10),spro varchar(10),Amount numeric(10,2))
insert into #b
select '00843', 'JD005', '101', 'abdd', 15.00 union all
select '00843', 'JD005', '101', 'abcd', 20.00 union all select '00843','JD006','101','adc',5.00 union all
select '00843','JD006','101','abcc',5.00 union all
select '00843','JD006','101','',1.00
select s1,s2,s3,sum(Amount),sum(Amount2) from
(
select s1,s2,s3,srow,Amount,0 Amount2 from #a
UNion all
select s1,s2,s3,spro,0,Amount from #b ) as t
group by s1,s2,s3
00843 JD006 101 0.00 11.00
00843 JE012 101 70.00 0.00