记录分时显示，谢谢

Question

在数据库里假如有10条记录
 1 name1 pic1 2011-1-1
 2 name2 pic2 2011-1-1
 3 name3 pic3 2011-8-1
 ............
 10 name10 pic10 2011-8-22现要求如下：
每隔4小时，记录要轮播，就是说，第一次显示顺序是1.2.3.4....10这样，但4小时后，显示顺序变为
2.3..。。10，1,最隔4小时，就变以 3.4.5。10.1.2不知要怎么实现呢

Answer 1

--用job，设置每隔4小时执行，假设第一列为id且可变
declare @n int
select top 1 @n=id from tb order by id
update tb set id=id+10 where id=@n
--顺序取
select * from tb order by id

Answer 2

declare @dt datetime
set @dt='2011-09-01 11:00:00 --四小时的起始点
select * from tb order by (case when id<=(datediff(hh,@dt,getdate())/4-1)%10+1 then id+10 else id end)

Answer 3

declare @dt datetime
set @dt='2011-09-01 11:00:00' --四小时的起始点
select * from tb order by (case when id<=(datediff(hh,@dt,getdate())/4-1)%10+1 then id+10 else id end)

Answer 4

create table tb(id int,name varchar(10),col1 varchar(10),dt datetime)
insert into tb select 1,'name1','pic1','2011-1-1'
insert into tb select 2,'name2','pic2','2011-1-1'
insert into tb select 3,'name3','pic3','2011-8-1'
insert into tb select 4,'name4','pic4','2011-1-1'
insert into tb select 5,'name5','pic5','2011-8-1'
insert into tb select 6,'name6','pic6','2011-1-1'
insert into tb select 7,'name7','pic7','2011-8-1'
insert into tb select 8,'name8','pic8','2011-1-1'
insert into tb select 9,'name9','pic9','2011-8-1'
insert into tb select 10,'name10','pic10','2011-8-22'
go
declare @dt datetime
set @dt='2011-09-01 7:00:00' --四小时的起始点
select * from tb order by (case when id<=(datediff(hh,@dt,getdate())/4-1)%10+1 then id+10 else id end)
/*
id          name       col1       dt
----------- ---------- ---------- -----------------------
2           name2      pic2       2011-01-01 00:00:00.000
3           name3      pic3       2011-08-01 00:00:00.000
4           name4      pic4       2011-01-01 00:00:00.000
5           name5      pic5       2011-08-01 00:00:00.000
6           name6      pic6       2011-01-01 00:00:00.000
7           name7      pic7       2011-08-01 00:00:00.000
8           name8      pic8       2011-01-01 00:00:00.000
9           name9      pic9       2011-08-01 00:00:00.000
10          name10     pic10      2011-08-22 00:00:00.000
1           name1      pic1       2011-01-01 00:00:00.000(10 行受影响)
*/
set @dt='2011-09-01 3:00:00' --四小时的起始点
select * from tb order by (case when id<=(datediff(hh,@dt,getdate())/4-1)%10+1 then id+10 else id end)
/*
id          name       col1       dt
----------- ---------- ---------- -----------------------
3           name3      pic3       2011-08-01 00:00:00.000
4           name4      pic4       2011-01-01 00:00:00.000
5           name5      pic5       2011-08-01 00:00:00.000
6           name6      pic6       2011-01-01 00:00:00.000
7           name7      pic7       2011-08-01 00:00:00.000
8           name8      pic8       2011-01-01 00:00:00.000
9           name9      pic9       2011-08-01 00:00:00.000
10          name10     pic10      2011-08-22 00:00:00.000
1           name1      pic1       2011-01-01 00:00:00.000
2           name2      pic2       2011-01-01 00:00:00.000(10 行受影响)*/
go
drop table tb

Answer 5

如 
id          name       col1       dt
----------- ---------- ---------- -----------------------
1           name1      pic1       2011-01-01 00:00:00.000
2           name2      pic2       2011-01-01 00:00:00.000
3           name3      pic3       2011-08-01 00:00:00.0006           name6      pic6       2011-01-01 00:00:00.0008           name8      pic8       2011-01-01 00:00:00.000
9           name9      pic9       2011-08-01 00:00:00.000
10          name10     pic10      2011-08-22 00:00:00.000

Answer 6

如果ID不连续,那要用row_number()函数给各行建一个连接的值.程序类似:
select * from(
select row_number()over(order by id) as rn from tb
)t order by (case when rn<=(datediff(hh,@dt,getdate())/4-1)%10+1 then rn+10 else id end)