现有两个表,要对其进行汇总后进行关联,可是第二个表关联后的数据老出问题,不知是怎么回事
比如表A:
ID value
A 4
A 7
B 2
C 3
B 6
汇总后结是:
A 11
B 8
C 3
表B:
ID value
A 5
A 3
B 9
汇总后结果是:
ID value
A 8
B 9
现在把两个表汇总后的记录进行关联,不用视图.得出结果应该是:
ID value value
A 11 8
B 8 9
C 3 NULl哪位大G能告诉我,怎么进行写SQL才能得到最后的结果呀?
比如表A:
ID value
A 4
A 7
B 2
C 3
B 6
汇总后结是:
A 11
B 8
C 3
表B:
ID value
A 5
A 3
B 9
汇总后结果是:
ID value
A 8
B 9
现在把两个表汇总后的记录进行关联,不用视图.得出结果应该是:
ID value value
A 11 8
B 8 9
C 3 NULl哪位大G能告诉我,怎么进行写SQL才能得到最后的结果呀?
SELECT ID,case WHEN TYPE='1' THEN sum(value) else 0 end ,case WHEN TYPE='2' THEN sum(value) else 0 end
FROM
(select '1' TYPE,A.ID,sum(A.value) value
FROM A
group by A.ID
union all
select '2',B.ID,sum(B.value)
FROM B
group by B.ID) A
FROM
(select '1' TYPE,A.ID,sum(A.value) value
FROM A
group by A.ID
union all
select '2',B.ID,sum(B.value)
FROM B
group by B.ID) A
GROUP BY id
insert into @a select 'A',4
UNION ALL
SELECT 'A',7
UNION ALL
SELECT 'B',2
UNION ALL
SELECT 'C',3
UNION ALL
SELECT 'B',6declare @b table (id char(1),value int)
insert into @b select 'A',5
UNION ALL
SELECT 'A',3
UNION ALL
SELECT 'B',9select * from @a
select * from @bselect a.id,a.value,b.value
from (SELECT ID,SUM(VALUE) AS VALUE FROM @A GROUP BY ID ) a
left join (SELECT ID,SUM(VALUE) AS VALUE FROM @B GROUP BY ID ) b
on a.id=b.id
ID value value
A 11 8
B 8 9
C 3 NULl
create table ta ([id] char(1),value int)
insert into ta values('a',4)
insert into ta values('a',7)
insert into ta values('b',2)
insert into ta values('c',3)
insert into ta values('b',6)create table tb ([id] char(1),value int)
insert into tb values('a',5)
insert into tb values('a',3)
insert into tb values('b',9)
select * from ta
select * from tbselect a.[id],a.value,b.value
from (select [id],sum(value) as value from ta group by [id]) a
left join (select [id],sum(value) as value from tb group by [id]) b
on a.[id]=b.[id]/*
id value value
---- ----------- -----------
a 11 8
b 8 9
c 3 NULL(所影响的行数为 3 行)
*/
insert @t select 'A',4
union all select 'A',7
union all select 'B',2
union all select 'C',10
union all select 'B',6
select * from @tdeclare @t1 table(id varchar,[value] int)
insert @t1 select 'A',11
union all select 'B',8
union all select 'A',8
select * from @t1select a.*,b.val from
(select id,sum([value]) as val from @t group by id) a left join
(select id,sum([value]) as val from @t1 group by id) b
on a.id = b.id