select 备件名称=A.bjname, 备件规格=A.bjspec, 备件编码=A.bjid, 入库数量=sum(inamount), 出库数量=sum(outamount), 月别=B.indate from A,B,C where B.bjid=C.bjid and indate=outdate and A.bjid=B.bjid group by B.bjid,indate
select B.bj_id,B.total,C.total2,B.indate2 from (select bj_id,convert(varchar(7),indate,20) as indate2,sum(isnull(inamount,0)) as total from instorage group by bj_id,convert(varchar(7),indate,20)) B, (select bj_id,convert(varchar(7),outdate,20) as indate3,sum(isnull(amount,0)) as total2 from outstorage group by bj_id,convert(varchar(7),outdate,20)) C where B.bj_id=C.bj_id order by B.bj_id 这个是我写的语句,其实把instorage即为B表,outstorage即为C表,A表可以暂时去掉 执行的结果还是达不到效果
--Create table Create table A(bjname varchar(10) ,bjspec varchar(10) ,bjid varchar(10) ,safetyamount int )Create table B(bjid varchar(10) ,indate varchar(10) ,inamount int )Create table C(bjid varchar(10) ,outdate varchar(10) ,outamount int )--Insert Data insert into A select '轴承','spec1','001',100insert into B select '001','2006-01',10 union all select '001','2006-02',0insert into C select '001','2006-01',8 union all select '001','2006-02',1--Test select 备件名称 = a.bjname ,备件规格 = a.bjspec ,备件编码 = a.bjid ,入库数量 = BC.inamount ,出库数量 = BC.outamount ,月别 = BC.ThisDate from A inner join ( select b.bjid,inamount = sum(b.inamount) ,outamount = sum(Case when c.outdate is null then 0 else c.outamount end) --考虑到出库月份在如库月份不存在的情况下,出库数量为0处理 ,ThisDate = b.indate from B left join C on B.bjid = C.bjid and B.indate = c.outdate group by b.bjid ,b.indate )BC on a.bjid = BC.bjid--Drop Table Drop Table A Drop Table B Drop Table C
--当出库月份或入库月份不存在时 --Create Table Create table A(bjname varchar(10) ,bjspec varchar(10) ,bjid varchar(10) ,safetyamount int )Create table B(bjid varchar(10) ,indate varchar(10) ,inamount int )Create table C(bjid varchar(10) ,outdate varchar(10) ,outamount int )--Insert Date insert into A select '轴承','spec1','001',100insert into B select '001','2006-01',10 union all select '001','2006-02',0 union all select '001','2006-03',10insert into C select '001','2006-01',8 union all select '001','2006-02',1 union all select '001','2006-04',5--Test --当出库月份或入库月份不存在时 select 备件名称 = a.bjname ,备件规格 = a.bjspec ,备件编码 = a.bjid ,入库数量 = BC.inamount ,出库数量 = BC.outamount ,月别 = BC.ThisDate from A inner join ( select bjid = max(Case when b.bjid is null then c.bjid when c.bjid is null then b.bjid else b.bjid end) ,inamount = sum(Case when b.indate is null then 0 else b.inamount end) ,outamount = sum(Case when c.outdate is null then 0 else c.outamount end) --考虑到出库月份或入库月份不存在的情况下,数量为0处理 ,ThisDate = max(Case when b.indate is null then c.outdate when c.outdate is null then b.indate else b.indate end) from B full join C on B.bjid = C.bjid and B.indate = c.outdate group by b.bjid ,b.indate )BC on a.bjid = BC.bjid order by BC.ThisDate--Drop Table Drop Table A Drop Table B Drop Table C
备件规格=A.bjspec,
备件编码=A.bjid,
入库数量=sum(inamount),
出库数量=sum(outamount),
月别=B.indate
from A,B,C
where B.bjid=C.bjid and indate=outdate and A.bjid=B.bjid
group by B.bjid,indate
(select bj_id,convert(varchar(7),indate,20) as indate2,sum(isnull(inamount,0)) as total from instorage
group by bj_id,convert(varchar(7),indate,20)) B,
(select bj_id,convert(varchar(7),outdate,20) as indate3,sum(isnull(amount,0)) as total2 from outstorage
group by bj_id,convert(varchar(7),outdate,20)) C
where B.bj_id=C.bj_id
order by B.bj_id
这个是我写的语句,其实把instorage即为B表,outstorage即为C表,A表可以暂时去掉
执行的结果还是达不到效果
Create table A(bjname varchar(10)
,bjspec varchar(10)
,bjid varchar(10)
,safetyamount int
)Create table B(bjid varchar(10)
,indate varchar(10)
,inamount int
)Create table C(bjid varchar(10)
,outdate varchar(10)
,outamount int
)--Insert Data
insert into A select '轴承','spec1','001',100insert into B select '001','2006-01',10
union all select '001','2006-02',0insert into C select '001','2006-01',8
union all select '001','2006-02',1--Test
select 备件名称 = a.bjname
,备件规格 = a.bjspec
,备件编码 = a.bjid
,入库数量 = BC.inamount
,出库数量 = BC.outamount
,月别 = BC.ThisDate
from A
inner join
(
select b.bjid,inamount = sum(b.inamount)
,outamount = sum(Case when c.outdate is null then 0 else c.outamount end) --考虑到出库月份在如库月份不存在的情况下,出库数量为0处理
,ThisDate = b.indate
from B
left join C
on B.bjid = C.bjid
and B.indate = c.outdate
group by b.bjid ,b.indate
)BC
on a.bjid = BC.bjid--Drop Table
Drop Table A
Drop Table B
Drop Table C
--Create Table
Create table A(bjname varchar(10)
,bjspec varchar(10)
,bjid varchar(10)
,safetyamount int
)Create table B(bjid varchar(10)
,indate varchar(10)
,inamount int
)Create table C(bjid varchar(10)
,outdate varchar(10)
,outamount int
)--Insert Date
insert into A select '轴承','spec1','001',100insert into B select '001','2006-01',10
union all select '001','2006-02',0
union all select '001','2006-03',10insert into C select '001','2006-01',8
union all select '001','2006-02',1
union all select '001','2006-04',5--Test
--当出库月份或入库月份不存在时
select 备件名称 = a.bjname
,备件规格 = a.bjspec
,备件编码 = a.bjid
,入库数量 = BC.inamount
,出库数量 = BC.outamount
,月别 = BC.ThisDate
from A
inner join
(
select bjid = max(Case when b.bjid is null then c.bjid when c.bjid is null then b.bjid else b.bjid end)
,inamount = sum(Case when b.indate is null then 0 else b.inamount end)
,outamount = sum(Case when c.outdate is null then 0 else c.outamount end) --考虑到出库月份或入库月份不存在的情况下,数量为0处理
,ThisDate = max(Case when b.indate is null then c.outdate when c.outdate is null then b.indate else b.indate end)
from B
full join C
on B.bjid = C.bjid
and B.indate = c.outdate
group by b.bjid ,b.indate
)BC
on a.bjid = BC.bjid
order by BC.ThisDate--Drop Table
Drop Table A
Drop Table B
Drop Table C
我实验了,如果把2月份入库的记录去掉,就发生了错误