用存储过程实现随机生成N条记录(原创)_存储过程实现能随机获取表格3条记录-优快云博客

本文介绍了一个SQL存储过程，用于从现有表中随机选择指定数量的记录，并将其存储在临时表中，确保了所选记录的唯一性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*测试表为:Office(OfficeID int,O_name varchar(50),O_type tinyint)
Write By zhubian QQ195986181
*/

create procedure dbo.RandSelect
(
@number int --输入参数(要生成的记录条数)
)
as
declare @tableString varchar(20)

create table #1(OfficeID int,O_name varchar(50),O_type tinyint) --存放随机生成的记录(临时表)
create table #2(rand_num int) --存入已放入#1的记录的OfficeID(实现不生成相同的两条记录)

declare @OfficeID int , @O_name varchar(50) , @O_type tinyint , @randnum int , @rowcount int , @i int

select @rowcount = (select count(*) from Office)
if @number<@rowcount
  begin
   set @i = @number   --要求随机生成的记录条数(输入参数)
  end
else
  begin
   set @i = @rowcount
  end

declare c cursor scroll for select * from Office
open c

while(@i>0)
begin

  declare @tempoid int--记录生成生的随机数是否重复
  set @randnum = rand()*@rowcount+1
  set @tempoid = (select count(*) from #2 where rand_num = @randnum)
  while( @tempoid <>0 )
   begin
    set @randnum = rand()*@rowcount+1
    set @tempoid = (select count(*) from #2 where rand_num = @randnum)
   end

  fetch absolute @randnum from c into @OfficeID,@O_name,@O_type
  insert into #1 values(@OfficeID,@O_name,@O_type)
  insert into #2 values(@randnum)
  set @i = @i-1
end

close c
deallocate c

select * from #1
drop table #1
drop table #2
GO

--exec RandSelect @number=150

--drop procedure dbo.RandSelect