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

2017-09-23 04:20:28 246 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

qq_27638253 墨衍会员 · AI 创作全网分发关注 + 加入社区

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

USED=false

...全文

qq_27638253

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

砸死牛顿的苹果 2017-09-23

打赏
举报

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顺序但是不一定连续的情况

二月十六 2017-09-23

打赏
举报

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

--测试数据

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

qq_27638253 2017-09-23

打赏
举报

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

二月十六 2017-09-23

打赏
举报

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