我现在库是这样的,
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:45 3
3 2011-05-28 11:30:45 7
4 2011-05-28 11:30:45 10
....................
我想得到这样的结果应该写SQL语句
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:48 3
3 2011-05-28 11:30:51 7
4 2011-05-28 11:30:58 7
..................................
以此类推。
应该怎么做好?请大虾帮帮忙。小弟在此谢谢了。
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:45 3
3 2011-05-28 11:30:45 7
4 2011-05-28 11:30:45 10
....................
我想得到这样的结果应该写SQL语句
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:48 3
3 2011-05-28 11:30:51 7
4 2011-05-28 11:30:58 10
..................................
以此类推。
应该怎么做好?请大虾帮帮忙。小弟在此谢谢了。
上面发错了
from tb a
declare @table table (id int,date datetime,bcms int)
insert into @table
select 1,'2011-05-28 11:30:45',3 union all
select 2,'2011-05-28 11:30:45',3 union all
select 3,'2011-05-28 11:30:45',7 union all
select 4,'2011-05-28 11:30:45',10select a.id,
date=dateadd(ss,(select isnull(sum(bcms),0) from @table where id<a.id),a.date),a.bcms
from @table a
/*
id date bcms
----------- ----------------------- -----------
1 2011-05-28 11:30:45.000 3
2 2011-05-28 11:30:48.000 3
3 2011-05-28 11:30:51.000 7
4 2011-05-28 11:30:58.000 10
*/
insert into tb select 1,'2011-05-28 11:30:45',3
insert into tb select 2,'2011-05-28 11:30:45',3
insert into tb select 3,'2011-05-28 11:30:45',7
insert into tb select 4,'2011-05-28 11:30:45',10
go
select id,dateadd(s,isnull((select sum(bcms) from tb where id<a.id),0),a.date)date,a.bcms from tb a
go
drop table tb
/*
id date bcms
----------- ----------------------- -----------
1 2011-05-28 11:30:45.000 3
2 2011-05-28 11:30:48.000 3
3 2011-05-28 11:30:51.000 7
4 2011-05-28 11:30:58.000 10(4 行受影响)
*/
我现在库是这样的,
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:45 3
3 2011-05-28 11:30:45 7
4 2011-05-28 11:30:45 10
5 2011-05-28 11:34:00 5
6 2011-05-28 11:34:00 10
....................
我想得到这样的结果应该写SQL语句
id date bcms
1 2011-05-28 11:30:45 3
2 2011-05-28 11:30:48 3
3 2011-05-28 11:30:51 7
4 2011-05-28 11:30:58 10
5 2011-05-28 11:34:00 5
6 2011-05-28 11:34:05 10 刚刚又发现 还要加个条件,就是当下一条时间不等于上一条时间加上保持秒数的时候,下一个时间就等于他自己,然后再以此类推。应该怎么改下呢?
insert into tb select 1,'2011-05-28 11:30:45',3
insert into tb select 2,'2011-05-28 11:30:45',3
insert into tb select 3,'2011-05-28 11:30:45',7
insert into tb select 4,'2011-05-28 11:30:45',10
insert into tb select 5,'2011-05-28 11:34:00',5
insert into tb select 6,'2011-05-28 11:34:00',10
go
select id,dateadd(s,isnull((select sum(bcms) from tb where id<a.id and date=a.date),0),a.date)date,a.bcms from tb a
go
drop table tb
/*
id date bcms
----------- ----------------------- -----------
1 2011-05-28 11:30:45.000 3
2 2011-05-28 11:30:48.000 3
3 2011-05-28 11:30:51.000 7
4 2011-05-28 11:30:58.000 10
5 2011-05-28 11:34:00.000 5
6 2011-05-28 11:34:05.000 10(6 行受影响)*/
date=dateadd(ss,(select isnull(sum(bcms),0) from @table where id<a.id),a.date),a.bcms
from @table a