怎样在数据库中随机查找连续三条没被使用的记录

[qq_27638253]

怎样在数据库中随机查找连续三条没被使用的记录，ID ,ID+1,ID+2

USED=false

[二月十六]

select top 3 字段 from 表 where used =FALSE order by newid()

[qq_27638253]

这只是随机查找后排序，但是ID不一定连续呀

[二月十六]

试试这样，这个是从每段连续的里边取前三个，每段是随机的，但是每段不是随机的

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[used] nvarchar(25))
Insert #T
select 1,N'true' union all
select 2,N'false' union all
select 3,N'false' union all
select 4,N'false' union all
select 5,N'false' union all
select 6,N'true' union all
select 7,N'ture' union all
select 8,N'false' union all
select 9,N'false' union all
select 10,N'false' union all
select 11,N'false' union all
select 12,N'false' union all
select 13,N'true'
Go
--测试数据结束
;WITH ctea AS (
SELECT  * ,
        id - ROW_NUMBER() OVER ( ORDER BY id ) AS num
FROM    #T
WHERE   used = 'false'
),cteb AS (
SELECT num,COUNT(1) AS rcount FROM ctea GROUP BY num HAVING COUNT(1)>2
)
SELECT TOP 3
        *
FROM    ctea
WHERE   num = ( SELECT TOP 1
                        num
                FROM    cteb
                ORDER BY NEWID()
              )
ORDER BY id

这个的结果有时候是234、有时候是8910

[砸死牛顿的苹果]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[used] nvarchar(25))
Insert #T
select 1,N'true' union all
select 2,N'false' union all
select 3,N'false' union all
select 4,N'false' union all
select 5,N'false' union all
select 6,N'true' union all
select 7,N'true' union all
select 8,N'false' union all
select 9,N'false' union all
select 10,N'false' union all
select 11,N'false' union all
select 12,N'false' union all
select 13,N'true'
Go

;with t as 
	(select pid=id-row_number()over(order by id),rid=row_number()over(order by id),* from #t where used='false')
select top 3 * from #t
where id>=(select top 1 id  from t t1 where exists(select 1 from t t2 where t1.pid=t2.pid and t2.id=t1.id+2) order by newid())
order by id

结果 5种可能 适合id顺序 但是不一定连续的情况