有表:A a char(10), b int(8)
'aa', 150
'aa', 200
'bb', 100
'cc', 100有表:B 字段a同A表中的a,
a char(10),b int(8),c char(10)
'aa', 100, T
'aa', 200, F
'bb', 200, T 欲求出这样的结果:'aa', sum(B.b) as b1 (条件 c='T'),sum(B.b) as b2 (条件 c='F'),sum(A.b) as b3请问这样的该如何做到?谢谢
'aa', 150
'aa', 200
'bb', 100
'cc', 100有表:B 字段a同A表中的a,
a char(10),b int(8),c char(10)
'aa', 100, T
'aa', 200, F
'bb', 200, T 欲求出这样的结果:'aa', sum(B.b) as b1 (条件 c='T'),sum(B.b) as b2 (条件 c='F'),sum(A.b) as b3请问这样的该如何做到?谢谢
A.a,
sum(case B.c when 'T' then B.b else 0 end) as b1,
sum(case B.c when 'T' then B.b else 0 end) as b2,
sum(A.b)
from
A,B
where
A.a=B.a
group by
A.a
A.a,
sum(case B.c when 'T' then B.b else 0 end) as b1,
sum(case B.c when 'F' then B.b else 0 end) as b2,
sum(A.b)
from
A,B
where
A.a=B.a
group by
A.a
insert into @A select 'aa',150
insert into @A select 'aa',200
insert into @A select 'bb',100
insert into @A select 'cc',100
declare @B table(a varchar(10),b int,c varchar(10))
insert into @B select 'aa',100,'T'
insert into @B select 'aa',200,'F'
insert into @B select 'bb',200,'T'
select
A.a,
sum(case B.c when 'T' then B.b else 0 end) as b1,
sum(case B.c when 'F' then B.b else 0 end) as b2,
sum(A.b) as b3
from
@A A,@B B
where
A.a=B.a
group by
A.a
/*
a b1 b2 b3
---------- ----------- ----------- -----------
aa 200 400 700
bb 200 0 100
*/
A.a,
sum(case B.c when 'T' then B.b else 0 end) as b1,
sum(case B.c when 'T' then A.b else 0 end) as b2,
sum(A.b)
from
A,B
where
A.a=B.a
group by
A.a
from a
left join
(
select a,sum(case c when 't' then b else 0 end) as b1,sum(case c when 'f' then b else 0 end) as b2
from b
group by a
) as t
on a.a=t.a
insert into @A select 'aa',150
insert into @A select 'aa',200
insert into @A select 'bb',100
insert into @A select 'cc',100
declare @B table(a varchar(10),b int,c varchar(10))
insert into @B select 'aa',100,'T'
insert into @B select 'aa',200,'F'
insert into @B select 'bb',200,'T'
select
isnull(A.a,B.a) a,B.b1,B.b2,A.b3
from
(select a,sum(b) as b3 from @A group by a)A
full join
(select
a,
sum(case c when 'T' then b else 0 end) as b1,
sum(case c when 'F' then b else 0 end) as b2
from @B group by a) B
on
A.a=B.a
/*
a b1 b2 b3
---------- ----------- ----------- -----------
aa 100 200 350
bb 200 0 100
cc NULL NULL 100
*/