Select Non-repeatable Random Records By TSQL

Declare @RandAry As Table
(
RandomInt Int
)

Declare @Count As Int
Declare @RandLoop AS Int = 0
Declare @RandTotal As Int = 10
Declare @RandNumber AS Int

Select @Count = Count(*) From dbo._cache_vLocationLookup(NOLOCK)


While @RandLoop < @RandTotal
Begin
Set @RandNumber = Ceiling(Rand()*@Count)
Insert Into @RandAry
Select( @RandNumber)
Where Not Exists (Select * From @RandAry Where RandomInt = @RandNumber)
Select @RandLoop =Count(*) From @RandAry

End

Select * From @RandAry Order By RandomInt ASC

SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY placekey) AS RowNum
FROM dbo._cache_vLocationLookup(NOLOCK)
) AS A INNER JOIN @RandAry On RowNum = RandomInt

 

 

posted @ 2012-05-25 22:29  Allen Xu  阅读(104)  评论(0编辑  收藏  举报