表格如下:
id time num
1 2010-1-30 12
2 2010-1-30 22
1 2010-1-31 12
2 2010-1-31 22
3 2010-1-31 1
4 2010-1-31 44
1 2010-2-1 1
2 2010-2-1 2
3 2010-2-1 3
4 2010-2-1 4 希望得到如下结果 前一天的累加num
id date num
1 2010-1-31 12
2 2010-1-31 22
3 2010-1-31 0
4 2010-1-31 0
1 2010-2-1 24
2 2010-2-1 44
3 2010-2-1 1
4 2010-2-1 44
1 2010-2-2 25
2 2010-2-2 46
3 2010-2-2 4
4 2010-2-2 48
id time num
1 2010-1-30 12
2 2010-1-30 22
1 2010-1-31 12
2 2010-1-31 22
3 2010-1-31 1
4 2010-1-31 44
1 2010-2-1 1
2 2010-2-1 2
3 2010-2-1 3
4 2010-2-1 4 希望得到如下结果 前一天的累加num
id date num
1 2010-1-31 12
2 2010-1-31 22
3 2010-1-31 0
4 2010-1-31 0
1 2010-2-1 24
2 2010-2-1 44
3 2010-2-1 1
4 2010-2-1 44
1 2010-2-2 25
2 2010-2-2 46
3 2010-2-2 4
4 2010-2-2 48
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int, [time] datetime , num int)
insert tb select
1 , '2010-1-30' , 12 union select
2 , '2010-1-30', 22 union select
1 , '2010-1-31', 12 union select
2 , '2010-1-31', 22 union select
3 , '2010-1-31', 1 union select
4 , '2010-1-31', 44 union select
1 , '2010-2-1' , 1 union select
2 , '2010-2-1' , 2 union select
3 , '2010-2-1' , 3 union select
4 , '2010-2-1' , 4
go
select id,date=time,ISNULL(ks,0) as num
from (
select p.id,p.time,l.ks
from (
select z.id,z.time,num=ISNULL(num,0)
from tb right join(
select * from
(select distinct ID from tb ) k ,(select distinct [time] from tb) l) z on tb.id=z.id and tb.time=z.time) p
cross apply (select SUM(num) as ks from tb where p.id=id and p.time>=time) l) l
id date num
----------- ----------------------- -----------
1 2010-01-30 00:00:00.000 12
2 2010-01-30 00:00:00.000 22
3 2010-01-30 00:00:00.000 0
4 2010-01-30 00:00:00.000 0
1 2010-01-31 00:00:00.000 24
2 2010-01-31 00:00:00.000 44
3 2010-01-31 00:00:00.000 1
4 2010-01-31 00:00:00.000 44
1 2010-02-01 00:00:00.000 25
2 2010-02-01 00:00:00.000 46
3 2010-02-01 00:00:00.000 4
4 2010-02-01 00:00:00.000 48
/*
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int, [time] datetime , num int)
insert tb select
1 , '2010-1-30' , 12 union select
2 , '2010-1-30', 22 union select
1 , '2010-1-31', 12 union select
2 , '2010-1-31', 22 union select
3 , '2010-1-31', 1 union select
4 , '2010-1-31', 44 union select
1 , '2010-2-1' , 1 union select
2 , '2010-2-1' , 2 union select
3 , '2010-2-1' , 3 union select
4 , '2010-2-1' , 4
go
select id,date=time,ISNULL(ks,0) as num
from (
select p.id,p.time,l.ks
from(
select * from
(select distinct ID from tb ) k ,(select distinct [time] from tb)p ) p
cross apply (select SUM(num) as ks from tb where p.id=id and p.time>=time) l) l
/*
id date num
----------- ----------------------- -----------
1 2010-01-30 00:00:00.000 12
2 2010-01-30 00:00:00.000 22
3 2010-01-30 00:00:00.000 0
4 2010-01-30 00:00:00.000 0
1 2010-01-31 00:00:00.000 24
2 2010-01-31 00:00:00.000 44
3 2010-01-31 00:00:00.000 1
4 2010-01-31 00:00:00.000 44
1 2010-02-01 00:00:00.000 25
2 2010-02-01 00:00:00.000 46
3 2010-02-01 00:00:00.000 4
4 2010-02-01 00:00:00.000 48*/
你参考一下看看对你有帮助吗?
create table st1
(
zd1 varchar(20),
zd2 varchar(20),
zd3 varchar(20),
zd4 varchar(20)
)
alter table st2
(
zd1 varchar(20), ---name
zd2 varchar(50), --id
zd3 varchar(20),
zd4 varchar(20),
zd5 varchar(20),
zd6 varchar(20)
)
insert into st1 values('zd1','zd2','zd3','zd4')
insert into st2 values('zd21','zd22','zd23','zd24','zd25','zd25')
select *from st1
select * from st2
insert into st2 select '山东',replace((convert(char(12),getdate(),112)+'_'+
convert(varchar(10),(select substring(convert(varchar(10),(select max(j) from (select convert(int,replace(zd2,'_','')) as j,* from st2) as t
)),9,20))+1)
),' ',''),* from st1
select * from st2
if object_id('tb') is not null drop table tb
create table tb(id int,
time datetime,
num int)insert into tb
select 1,'2010-1-30',12 union all
select 2,'2010-1-30',22 union all
select 1,'2010-1-31',12 union all
select 2,'2010-1-31',22 union all
select 3,'2010-1-31',1 union all
select 4,'2010-1-31',44 union all
select 1,'2010-2-1',1 union all
select 2,'2010-2-1',2 union all
select 3,'2010-2-1',3 union all
select 4,'2010-2-1',4select t1.id,t1.time,isnull(t2.num,0)
from
(select a.id,b.time from
(select distinct id from tb) a,(select distinct dateadd(dd,1,time) as time from tb) b) t1 left outer join
(select a.id,dateadd(dd,1,a.time) time,(select sum(num) from tb b where id=a.id and time<=a.time) num
from tb a ) t2
on t1.id=t2.id and t1.time=t2.timeid time
----------- ----------------------- -----------
1 2010-01-31 00:00:00.000 12
2 2010-01-31 00:00:00.000 22
3 2010-01-31 00:00:00.000 0
4 2010-01-31 00:00:00.000 0
1 2010-02-01 00:00:00.000 24
2 2010-02-01 00:00:00.000 44
3 2010-02-01 00:00:00.000 1
4 2010-02-01 00:00:00.000 44
1 2010-02-02 00:00:00.000 25
2 2010-02-02 00:00:00.000 46
3 2010-02-02 00:00:00.000 4
4 2010-02-02 00:00:00.000 48
(select distinct id from tb) as a
cross join
(select distinct dateadd(dd,1,time) as time from tb) as b
left join
(
select id,dateadd(dd,1,time) as time,sum(num) as num from tb group by id,time
) as c on a.id=c.id and c.time=b.time
order by b.time,a.id
1 2010-1-30 12
2 2010-1-30 22
3 2010-1-30 1
4 2010-1-30 44
1 2010-1-31 12
2 2010-1-31 22
1 2010-2-1 1
2 2010-2-1 2
3 2010-2-1 3
4 2010-2-1 4 希望得到如下结果id date num 1 2010-1-31 12
2 2010-1-31 22
3 2010-1-31 1
4 2010-1-31 44
1 2010-2-1 24
2 2010-2-1 44
3 2010-2-1 1
4 2010-2-1 44
1 2010-2-2 25
2 2010-2-2 46
3 2010-2-2 4
4 2010-2-2 48
if object_id('tb') is not null drop table tb
create table tb(id int,
time datetime,
num int)insert into tb
select 1,'2010-1-30',12 union all
select 2,'2010-1-30',22 union all
select 3,'2010-1-30',1 union all
select 4,'2010-1-30',44 union all
select 1,'2010-1-31',12 union all
select 2,'2010-1-31',22 union all
select 1,'2010-2-1',1 union all
select 2,'2010-2-1',2 union all
select 3,'2010-2-1',3 union all
select 4,'2010-2-1',4select t1.id,t1.time,(select sum(num) from tb where id=t1.id and time<t1.time) num
from
(select a.id,b.time from
(select distinct id from tb) a,(select distinct dateadd(dd,1,time) as time from tb) b) t1 left outer join
(select a.id,dateadd(dd,1,a.time) time, num
from tb a ) t2
on t1.id=t2.id and t1.time=t2.time
id time num
----------- ----------------------- -----------
1 2010-01-31 00:00:00.000 12
2 2010-01-31 00:00:00.000 22
3 2010-01-31 00:00:00.000 1
4 2010-01-31 00:00:00.000 44
1 2010-02-01 00:00:00.000 24
2 2010-02-01 00:00:00.000 44
3 2010-02-01 00:00:00.000 1
4 2010-02-01 00:00:00.000 44
1 2010-02-02 00:00:00.000 25
2 2010-02-02 00:00:00.000 46
3 2010-02-02 00:00:00.000 4
4 2010-02-02 00:00:00.000 48(12 row(s) affected)
select *, num = (select isnull(sum(num), 0) from tb c where a.id = c.id and c.time < b.time)
from (select distinct id from tb) a
cross join (select distinct time = time + 1 from tb) b