表book_base (书籍总表)
code name type Amount
1 aaa 音乐 100
2 bbb 体育 150
3 ccc 计算机 300
4 ddd 物理 120表book_in (书籍进货表)
code in_date in_amount
1 2010-01-01 10
2 2010-02-02 50
1 2010-02-04 30
1 2010-02-08 80
3 2010-02-10 80表boook_out (书籍出货表)
code out_date out_Amount
1 2010-02-11 10
1 2010-02-12 20
2 2010-02-21 15我想实现这样的效果,统计2月份的进货,出货报表,如下
code name in_amount out_amount balance
1 aaa 110 30 80
2 bbb 50 15 35
3 ccc 80 0 80code为4的书2月份没有进货和出货则不会体现在报表里请问该怎么实现?谢谢大家·
code name type Amount
1 aaa 音乐 100
2 bbb 体育 150
3 ccc 计算机 300
4 ddd 物理 120表book_in (书籍进货表)
code in_date in_amount
1 2010-01-01 10
2 2010-02-02 50
1 2010-02-04 30
1 2010-02-08 80
3 2010-02-10 80表boook_out (书籍出货表)
code out_date out_Amount
1 2010-02-11 10
1 2010-02-12 20
2 2010-02-21 15我想实现这样的效果,统计2月份的进货,出货报表,如下
code name in_amount out_amount balance
1 aaa 110 30 80
2 bbb 50 15 35
3 ccc 80 0 80code为4的书2月份没有进货和出货则不会体现在报表里请问该怎么实现?谢谢大家·
a.name ,
isnull((select sum(in_amount) from book_in where convert(varchar(7),in_date,120) = '2010-02' and code = a.code),0) in_amount,
isnull((select sum(out_Amount) from book_in where convert(varchar(7),out_date,120) = '2010-02' and code = a.code),0) out_Amount,
isnull((select sum(in_amount) from book_in where convert(varchar(7),in_date,120) = '2010-02' and code = a.code),0) -
isnull((select sum(out_Amount) from book_in where convert(varchar(7),out_date,120) = '2010-02' and code = a.code),0) balance
from book_base a
insert into book_base values(1 , 'aaa' , '音乐' , 100 )
insert into book_base values(2 , 'bbb' , '体育' , 150 )
insert into book_base values(3 , 'ccc' , '计算机', 300 )
insert into book_base values(4 , 'ddd' , '物理' , 120 )
create table book_in(code int, in_date datetime, in_amount int)
insert into book_in values(1 , '2010-01-01' , 10 )
insert into book_in values(2 , '2010-02-02' , 50 )
insert into book_in values(1 , '2010-02-04' , 30 )
insert into book_in values(1 , '2010-02-08' , 80 )
insert into book_in values(3 , '2010-02-10' , 80 )
create table book_out(code int, out_date datetime, out_Amount int)
insert into book_out values(1 , '2010-02-11' , 10 )
insert into book_out values(1 , '2010-02-12' , 20 )
insert into book_out values(2 , '2010-02-21' , 15 )
goselect a.code ,
a.name ,
isnull((select sum(in_amount) from book_in where convert(varchar(7),in_date,120) = '2010-02' and code = a.code),0) in_amount,
isnull((select sum(out_Amount) from boook_out where convert(varchar(7),out_date,120) = '2010-02' and code = a.code),0) out_Amount,
isnull((select sum(in_amount) from book_in where convert(varchar(7),in_date,120) = '2010-02' and code = a.code),0) -
isnull((select sum(out_Amount) from boook_out where convert(varchar(7),out_date,120) = '2010-02' and code = a.code),0) balance
from book_base adrop table book_base , book_in , book_out/*
code name in_amount out_Amount balance
----------- ---------- ----------- ----------- -----------
1 aaa 110 30 80
2 bbb 50 15 35
3 ccc 80 0 80
4 ddd 0 0 0(所影响的行数为 4 行)*/
(
code int,
[name] varchar(30),
[type] varchar(30),
Amount int
)
insert into #book_base1 select 1,'aaa','音乐',100
insert into #book_base1 select 2,'bbb','体育',150
insert into #book_base1 select 3,'ccc','计算机',300
insert into #book_base1 select 4,'ddd','物理',120create table #book_in
(
code int,
in_date datetime,
in_amount int
)
insert into #book_in select 1,'2010-01-01',10
insert into #book_in select 2,'2010-02-02',50
insert into #book_in select 1,'2010-02-04',30
insert into #book_in select 1,'2010-02-08',80
insert into #book_in select 3,'2010-02-10',80
create table #boook_out
(
code int,
in_date datetime,
in_amount int
)
insert into #boook_out select 1,'2010-01-01',10
insert into #boook_out select 1,'2010-02-12',20
insert into #boook_out select 2,'2010-02-21',15select b.name,I.code,sum(I.in_amount) in_amount,sum(O.out_amount) out_amount,sum(I.in_amount-isnuLL(O.out_amount,0)) balance
from #book_base1 B
join
(
select code,sum(in_amount) in_amount from #book_in group by code
) I
on B.code=I.code
left join
(
select code,sum(in_amount) out_amount from #boook_out group by code
)
O
on I.code=O.code
group by b.name,I.code
name code in_amount out_amount balance
------------------------------ ----------- ----------- ----------- -----------
aaa 1 120 30 90
bbb 2 50 15 35
ccc 3 80 NULL 80
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
from #book_base1 B
join
(
select code,sum(in_amount) in_amount from #book_in where convert(varchar(7),in_date,120)='2010-02' group by code
) I
on B.code=I.code
left join
(
select code,sum(in_amount) out_amount from #boook_out group by code
)
O
on I.code=O.code
group by b.name,I.code
name code in_amount out_amount balance
------------------------------ ----------- ----------- ----------- -----------
aaa 1 110 30 80
bbb 2 50 15 35
ccc 3 80 NULL 80
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
再次修改
select b.name,I.code,sum(I.in_amount) in_amount,sum(O.out_amount) out_amount,sum(I.in_amount-isnuLL(O.out_amount,0)) balance
from #book_base1 B
join
(
select code,sum(in_amount) in_amount from #book_in where convert(varchar(7),in_date,120)='2010-02' group by code
) I
on B.code=I.code
left join
(
select code,sum(in_amount) out_amount from #boook_out1 where convert(varchar(7),in_date,120)='2010-02' group by code
)
O
on I.code=O.code
group by b.name,I.code
name code in_amount out_amount balance
------------------------------ ----------- ----------- ----------- -----------
aaa 1 110 30 80
bbb 2 50 15 35
ccc 3 80 NULL 80
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
--建表
if object_id('book_base') is not null drop table book_base
create table book_base
(code int,[name] varchar(50),type varchar(20),Amount int)
goif object_id('book_in') is not null drop table book_base
create table book_in
(code int,in_date datetime,in_amount int)
goif object_id('boook_out') is not null drop table book_base
create table boook_out
(code int,out_date datetime,out_Amount int)
go--插入数据
insert into book_base
select '1','aaa','音乐','100' union all
select '2','bbb','体育','150' union all
select '3','ccc','计算机','300' union all
select '4','ddd','物理','120'insert into book_in
select '1','2010-01-01','10' union all
select '2','2010-02-02','50' union all
select '1','2010-02-04','30' union all
select '1','2010-02-08','80' union all
select '3','2010-02-10','80' insert into boook_out
select '1','2010-02-11','10' union all
select '1','2010-02-12','20' union all
select '2','2010-02-21','15' --查询
select t1.code,t1.[name]
,in_amount=(select sum(in_amount) from book_in t2 where t1.code=t2.code and datepart(mm,in_date)=2)
,out_amount=(select isnull(sum(out_Amount),0) from boook_out t3 where t1.code=t3.code and datepart(mm,out_date)=2)
,balance=(select sum(in_amount) from book_in t2 where t1.code=t2.code and datepart(mm,in_date)=2)-(select isnull(sum(out_Amount),0) from boook_out t3 where t1.code=t3.code and datepart(mm,out_date)=2)
from book_base t1
where exists(select 1 from book_in where t1.code=code)
or exists(select 1 from boook_out where t1.code=code)--结果
/*
code name in_amount out_amount balance
1 aaa 110 30 80
2 bbb 50 15 35
3 ccc 80 0 80
*/
create table book_base(code int, name varchar(10), type varchar(10), Amount int)
insert into book_base values(1 , 'aaa' , '音乐' , 100 )
insert into book_base values(2 , 'bbb' , '体育' , 150 )
insert into book_base values(3 , 'ccc' , '计算机', 300 )
insert into book_base values(4 , 'ddd' , '物理' , 120 )
create table book_in(code int, in_date datetime, in_amount int)
insert into book_in values(1 , '2010-01-01' , 10 )
insert into book_in values(2 , '2010-02-02' , 50 )
insert into book_in values(1 , '2010-02-04' , 30 )
insert into book_in values(1 , '2010-02-08' , 80 )
insert into book_in values(3 , '2010-02-10' , 80 )
create table book_out(code int, out_date datetime, out_Amount int)
insert into book_out values(1 , '2010-02-11' , 10 )
insert into book_out values(1 , '2010-02-12' , 20 )
insert into book_out values(2 , '2010-02-21' , 15 )--语句
select * from (select a.code,a.name,
isnull((select sum(in_amount) from book_in where a.code=code and convert(char(6),in_date,112)='201002'),0) as in_amount,
isnull((select sum(out_Amount) from book_out where a.code=code and convert(char(6),out_date,112)='201002'),0) as out_Amount,
isnull((select sum(in_amount) from book_in where a.code=code and convert(char(6),in_date,112)='201002'),0)-isnull((select sum(out_Amount) from book_out where a.code=code and convert(char(6),out_date,112)='201002'),0)as balance
from book_base a ) t
where in_amount<>'' or out_Amount<>''