create table tb(tid varchar(6),rq datetime,rk int,cl int)
Insert into tb
Select 1,'2009-8-11',100,20 union all
Select 1,'2009-10-11',0,50 union all
Select 1,'2009-11-11',10,60
想的到的结果是：
rq tid 期初数量 rl cl 期末数量
200908 1 0 100 20 80
200909 1 80 0 0 80
200910 1 80 0 50 30
200911 1 30 10 60 -20
.................
rl就是进的数量，cl就是出的数量，200908的期初数量为0是因为从8月开始
SQL code:
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table tb(tid varchar(6),rq datetime,rk int,cl int)
go
Insert into tb
Select 1,'2009-8-11',100,20 union all
Select 1,'2009-10-11',0,50 union all
Select 1,'2009-11-11',10,60
go
---查询---
select
convert(varchar(8),rq,112) as rq,
tid,
期初数量=isnull((select sum(rk-cl) from tb where tid=t.tid and rq<t.rq),0),
rk,
cl,
期末数量=isnull((select sum(rk-cl) from tb where tid=t.tid and rq<t.rq)
select convert(varchar(50),cast(convert(numeric(18,2),1275674000000/100000000)as real))+'亿'
select cast(convert(numeric(18,2),1275674000000/100000000)as real)
为什么上一个用varchar转换后就只保留了� ......
