表a
book_id book_name
1 AAAA
2 BBBB
3 CCCC表b
book_id type amount
1 in 20
1 in 10
1 out 5
1 out 15
2 in 10
2 out 5
2 out 3我想要这样的结果
book_id book_name in_amount out_amount
1 AAAA 30 20
2 BBBB 10 8
····请问该怎么写?
book_id book_name
1 AAAA
2 BBBB
3 CCCC表b
book_id type amount
1 in 20
1 in 10
1 out 5
1 out 15
2 in 10
2 out 5
2 out 3我想要这样的结果
book_id book_name in_amount out_amount
1 AAAA 30 20
2 BBBB 10 8
····请问该怎么写?
select a.book_id,a.book_name,
sum(case when b.[type] = 'in' then amount else 0 end) [in_amount],
sum(case when b.[type] = 'out' then amount else 0 end) [out_amount]
from a join b on a.book_id = b.book_id
group by a.book_id,b.book_id,a.book_name
in_amount=sum(case when b.type='in' then b.amount end),
out_amount=sum(case when b.type='in' then b.amount end)
from a,b where a.book_id=b.book_id group by a.book_id,a.book_name
insert into t1 select 1,'AAAA'
insert into t1 select 2,'BBBB'
insert into t1 select 3,'CCCC'
create table t2(book_id int,type varchar(10),amount int)
insert into t2 select 1,'in',20
insert into t2 select 1,'in',10
insert into t2 select 1,'out',5
insert into t2 select 1,'out',15
insert into t2 select 2,'in',10
insert into t2 select 2,'out',5
insert into t2 select 2,'out',3
go
select a.book_id,a.book_name,
sum(case when b.type='in' then b.amount else 0 end) as in_amount,
sum(case when b.type='out' then b.amount else 0 end) as out_amount
from t1 a inner join t2 b on a.book_id=b.book_id
group by a.book_id,a.book_name
/*
book_id book_name in_amount out_amount
----------- ---------- ----------- -----------
1 AAAA 30 20
2 BBBB 10 8(2 行受影响)
*/
go
drop table t1,t2
select aa.*,bb.in_amount,bb.out_amount
from 表a aa,
(select book_id ,
sum(case when type='in' then isnull(amount,0) else 0 end) as in_amount,
sum(case when type='out' then isnull(amount,0) else 0 end) as out_amount
from 表b
group by book_id)bb
where aa.book_id=bb.book_id