表格如下:
id time num
01 9:01
01 9:02
01 9:04
01 9:06
01 9:10
01 9:12
如取4条希望得到结果
01 9:01
01 9:02
01 9:04
01 9:06
01 9:02
01 9:04
01 9:06
01 9:10 01 9:04
01 9:06
01 9:10
01 9:12
id time num
01 9:01
01 9:02
01 9:04
01 9:06
01 9:10
01 9:12
如取4条希望得到结果
01 9:01
01 9:02
01 9:04
01 9:06
01 9:02
01 9:04
01 9:06
01 9:10 01 9:04
01 9:06
01 9:10
01 9:12
declare @T table(id varchar(2),time varchar(10))
insert @T
select '01','9:01' union all
select '01','9:02' union all
select '01','9:04' union all
select '01','9:06' union all
select '01','9:10' union all
select '01','9:12'select row_num=identity(int),* into #T from @T order by timedeclare @sql varchar(8000),@num int,@i int
select @num=4,@i=0
while @i<=(ident_current('tempdb.dbo.#T')-@num)
begin
set @sql=isnull(@sql+char(10)+'union all'+char(10),'')+'select top '+ltrim(@num)+' id,time from #T where row_num>'+ltrim(@i)
set @i=@i+1
end
exec(@sql)--删除临时表
drop table #T
id time
---- ----------
01 9:01
01 9:02
01 9:04
01 9:06
01 9:02
01 9:04
01 9:06
01 9:10
01 9:04
01 9:06
01 9:10
01 9:12
*/
insert @T
select '01','9:01' union all
select '01','9:02' union all
select '01','9:04' union all
select '01','9:06' union all
select '01','9:10' union all
select '01','9:12'select *,identity(int) nid into # from @t order by time
select top 4 * from # where nid>=(select cast(rand(checksum(newid()))*(max(nid)-3) as int) from #)
drop table #
declare @T table(id varchar(2),time varchar(10))
insert @T
select '01','9:01' union all
select '01','9:02' union all
select '01','9:04' union all
select '01','9:06' union all
select '01','9:10' union all
select '01','9:12'
select top 4 * from @t x where time>=(select top 1 time from @t a where 3<(select count(*) from @t b where b.time>=a.time) order by newid())
具体语句我就不写了,GF喊吃饭.
--原始数据:@T
declare @T table(id varchar(2),time varchar(10))
insert @T
select '01','9:01' union all
select '01','9:02' union all
select '01','9:04' union all
select '01','9:06' union all
select '01','9:10' union all
select '01','9:12'declare @num int,@row_num int
set @num=4select row_num=identity(int),* into #T from @T order by time
set @row_num=cast((ident_current('tempdb.dbo.#T')-@num+1)*rand() as int)--哦,随机N条连续的:
exec ('select top '+@num+' id,time from #T where row_num>'+@row_num)
/*
id time
---- ----------
01 9:02
01 9:04
01 9:06
01 9:10
*/--删除临时表
drop table #T
是否指定一时间,要得到>=指定时间后的连接几条记录.
查询的关键是对时间排序.
首先要知道时间的格式
对>9的时间,是否表示为hh:mm的格式的字符串,如是,
对时间排序可用
order by right('0'+time,5)
对时间排序
请LZ再详细说明要查询的结果.