简单思路,具体的情况和数据类型,你自己来转换了.declare @num int set @num=0 select WareID,StorageID, (case when type ='类型1' then @num=@num+convert(int,number) when type ='类型4' then @num=@num+convert(int,number) when type ='类型2' then @num=@num-convert(int,number) when type ='类型3' then @num=@num-convert(int,number) end) from StorageInf_Detail
--建立测试环境 Create Table 表(WareID varchar(10),StorageID varchar(10),number integer,type varchar(10),date varchar(10)) --插入数据 insert into 表 select '1','2','3','4','2005-03-29' union select '1','2','5','3','2005-03-29' union select '1','2','7','2','2005-03-29' union select '1','2','1','1','2005-03-29' select * from 表 --测试语句 select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum from 表 group by WareID,StorageID,date --删除测试环境 Drop Table 表
--建立测试环境 Create Table StorageInf_Detail(WareID varchar(10),StorageID varchar(10),number integer,type varchar(10),date varchar(10)) --插入数据 insert into StorageInf_Detail select '1','2','3','4','2005-03-29' union select '1','2','5','3','2005-03-29' union select '1','2','7','2','2005-03-29' union select '1','2','1','1','2005-03-29' select * from StorageInf_Detail --测试语句 select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum from StorageInf_Detail group by WareID,StorageID,date --删除测试环境 --Drop Table StorageInf_Detail/*WareID StorageID date sumnum ---------- ---------- ---------- ----------- 1 2 2005-03-29 -8 */
我还要得到其余两列,通过时间限制求出一定时间范围中的number 如何做
你都要放到一个语句里? select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum, yourneed = (select 语句) from StorageInf_Detail group by WareID,StorageID,date
select aa.clmc as 材料名称,aa.model as 规格,aa.unit as 材料单位,bb.TypeName as 类别名称,cc.sumnum as 期初数量,dd.sumnum as 入库数量 ,ee.sumnum as 出库数量,ff.sumnum as 期末数量,gg.Storagename as 仓库 from (select * from Table_wlbm) as aa left join (select * from Table_wllb) as bb on (aa.typeid=bb.typeid) left join (select WareID,StorageID,sum(case when SignIn in('2','3') then -number else number end) as sumnum from StorageInf_Detail group by WareID,StorageID) as cc on (cc.wareid=aa.cl_id) left join (select WareID,StorageID,sum(case when SignIn in('1') then number end) as sumnum from StorageInf_Detail group by WareID,StorageID) as dd on (dd.wareid=cc.WareID and dd.StorageID=cc.StorageID) left join (select WareID,StorageID,sum(case when SignIn in('2') then number end) as sumnum from StorageInf_Detail group by WareID,StorageID) as ee on (ee.wareid=dd.WareID and ee.StorageID=dd.StorageID) left join (select WareID,StorageID,sum(case when SignIn in('2','3') then -number else number end) as sumnum from StorageInf_Detail group by WareID,StorageID) as ff on (ff.wareid=ee.WareID and ff.StorageID=ee.StorageID) left join (select * from StorageInf) as gg on (gg.StorageID=cc.StorageID and gg.StorageID=dd.StorageID and gg.StorageID=ee.StorageID and gg.StorageID=ff.StorageID)ok了,看看行不行
set @num=0
select WareID,StorageID,
(case when type ='类型1' then @num=@num+convert(int,number)
when type ='类型4' then @num=@num+convert(int,number)
when type ='类型2' then @num=@num-convert(int,number)
when type ='类型3' then @num=@num-convert(int,number)
end)
from StorageInf_Detail
Create Table 表(WareID varchar(10),StorageID varchar(10),number integer,type varchar(10),date varchar(10))
--插入数据
insert into 表
select '1','2','3','4','2005-03-29' union
select '1','2','5','3','2005-03-29' union
select '1','2','7','2','2005-03-29' union
select '1','2','1','1','2005-03-29'
select * from 表
--测试语句
select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum
from 表 group by WareID,StorageID,date
--删除测试环境
Drop Table 表
Create Table StorageInf_Detail(WareID varchar(10),StorageID varchar(10),number integer,type varchar(10),date varchar(10))
--插入数据
insert into StorageInf_Detail
select '1','2','3','4','2005-03-29' union
select '1','2','5','3','2005-03-29' union
select '1','2','7','2','2005-03-29' union
select '1','2','1','1','2005-03-29'
select * from StorageInf_Detail
--测试语句
select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum
from StorageInf_Detail group by WareID,StorageID,date
--删除测试环境
--Drop Table StorageInf_Detail/*WareID StorageID date sumnum
---------- ---------- ---------- -----------
1 2 2005-03-29 -8
*/
如何做
select WareID,StorageID,date,sum(case when type in('2','3') then -number else number end) as sumnum,
yourneed = (select 语句)
from StorageInf_Detail group by WareID,StorageID,date
(select * from Table_wlbm)
as aa
left join
(select * from Table_wllb)
as bb
on (aa.typeid=bb.typeid)
left join
(select WareID,StorageID,sum(case when SignIn in('2','3') then -number else number end) as sumnum
from StorageInf_Detail group by WareID,StorageID)
as cc
on (cc.wareid=aa.cl_id)
left join
(select WareID,StorageID,sum(case when SignIn in('1') then number end) as sumnum
from StorageInf_Detail group by WareID,StorageID)
as dd
on (dd.wareid=cc.WareID and dd.StorageID=cc.StorageID)
left join
(select WareID,StorageID,sum(case when SignIn in('2') then number end) as sumnum
from StorageInf_Detail group by WareID,StorageID)
as ee
on (ee.wareid=dd.WareID and ee.StorageID=dd.StorageID)
left join
(select WareID,StorageID,sum(case when SignIn in('2','3') then -number else number end) as sumnum
from StorageInf_Detail group by WareID,StorageID)
as ff
on (ff.wareid=ee.WareID and ff.StorageID=ee.StorageID)
left join
(select * from StorageInf)
as gg
on (gg.StorageID=cc.StorageID and gg.StorageID=dd.StorageID and gg.StorageID=ee.StorageID and gg.StorageID=ff.StorageID)ok了,看看行不行