在该表中增加一个字段用于标志记录号,在建立一个表用于存放使用随机函数生成的值。使用如下语句就可搞定:
select * from table1 where id in (select id from table2)
不过产生随机数比较麻烦,我就不写了,你应该可以编吧
select * from table1 where id in (select id from table2)
不过产生随机数比较麻烦,我就不写了,你应该可以编吧
(
id int identity(1,1) not null,
pk_youtable int not null,
)
insert into aa# select pk_youtable from yourtable
DECLARE @counter smallint,@getrand int, @returnstring varchar(1000),@isin smallint
SET @counter = 1
set @returnstring=""
WHILE @counter < 51
BEGIN
SELECT @getrand=convert(int,RAND()*1000)
set @isin=CHARINDEX(","+convert(varchar(4),@getrand)+",",@returnstring)
if @isin=0
select @returnstring=@returnstring+convert(varchar(4),@getrand)+","
else
CONTINUE
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
select @returnstring="("+left(@returnstring,len(@returnstring)-1)+")"
exec ("select pk_youtable from aa# where id in"+@returnstring)
方法我试过了,完全通过,只是效率方面不行啊,我这里的记录数可能是上万的,@returnstring列表不能容纳,将51改成了4000,运行了几分钟还没有出结果。我不相信这个问题没有好方法,应该在系统设计中很多人都会碰到。
select @count=cast((RAND() * 1000) as int) %1000
set Rowcount @count /*只取@count條記錄 */
select * from suppliers
你误会我的意思了,记录数是N,而记录是表中随机的
#Tmps 用來存你的table中的關鍵字,Filter 字段用來防止記錄被重複選到
樣例表Suppliers的Key值為SupplierID,
*/Create table #Tmps (
SupplierID Int Not null,
Filter bit default (0) /*標記是否已經選取過*/
)
GoDeclare @count INT,@counter INT,@position INT,@Rowcount INT ,@ID INTInsert into #Tmps (SupplierID,Filter) select Suppliers.SupplierID,0 As Filter from Suppliers
Set @Rowcount =@@Rowcount /*總記錄數*/Set Nocount ONDeclare abc CURSOR Scroll FOR
Select SupplierID from Suppliers Open abc
Set @counter =1
Set @count=cast(Rand() * 1000 as INT ) % 1000 /* 如果有1000筆記錄*/fetch next from abc into @ID
WHILE @counter<=@count AND @@Fetch_status=0
Begin
Set @position=cast(Rand() * @Rowcount AS INT ) % @Rowcount
fetch ABSOLUTE @position from abc into @ID /* 隨機取第@position筆*/
Update #tmps Set Filter =1 WHERE SupplierID=@ID AND Filter =0 /*選中這一筆*/
Set @counter =@counter +1
End
--得到結果
Select Suppliers.* from #Tmps INNER JOIN Suppliers ON #Tmps.SupplierID=Suppliers.SupplierID WHERE #Tmps.Filter=1
Drop table #Tmps
Deallocate abc
Set Nocount OFF
改成
if @@rowcount=1 Set @counter=@counter+1 /*保証取到N筆而不會重複*/
create table aa#
(
id int identity(1,1) not null,
pk_youtable int not null,
)
create table bb#
(
aaID int not null
)insert into aa# select pk_youtable from yourtable
DECLARE @counter smallint,@getrand int, @irowcount int
select @irowcount=count(*) from aa#
SET @counter = 1
WHILE @counter < 51
BEGIN
-- SELECT @getrand=convert(int,RAND()*@irowcount)
insert into bb# select convert(int,RAND()*@irowcount)
END
select youtable.fieldname1,youtable.fieldname2 from bb# b,aa# a,youtablename c
where
b.aaid=a.id and
a.pk_youtable=c.pk_youtable
BEGIN
SELECT @getrand=convert(int,RAND()*@irowcount)
set @aaid=0
select @aaid=aaid from bb# where aaid=@getrand
if @aaid=0
insert into bb# select @getrand
else
continue
SET @counter = @counter + 1
END
比如:
生成23,34,67,120
2)有这样一个表table1
ID 顺序号(1-1000)
PKEY 主键
那么:
select * from tablename
where pkey in(select pkey from table1 where id in(23,34,67,120));
得到的就是随机数记录吗!
如果table1中有些記錄被刪除了,那ID連不是有序的了,又必須額外的把ID重新編號。
如果你要n条纪录,第一条是随机取出的,以后的n条就算是连续取的,是不是也应该算是随机的呢?我想这是一个变通的做法吧,这样会容易些。
1.原表(visitor)中加入一列V_RanOrder,缺省值为0;
2.原理,将V_RanOrder用随机数填充,然后按照V_RanOrder排序,取最前N条记录即可。
3.建立存储过程CREATE PROCEDURE proc_test
@N INT = 100
ASDECLARE @iStart INT
DECLARE @iEnd INT
DECLARE @iCount INT
DECLARE @iValue INT
DECLARE @sSQL VARCHAR(8000)SET @iCount = 1
SET @sSQL = ''//取最小ID
SELECT TOP 1 @iStart = v_id FROM visitor ORDER BY v_id ASC
//取最大ID
SELECT TOP 1 @iEnd = v_id FROM visitor ORDER BY v_id DESC//一定的循环
WHILE @iCount < @iEnd / 5
BEGIN
SET @iValue = CONVERT(INT, RAND() * (@iEnd - @iStart + 1) + @iStart)
UPDATE Visitor SET V_RanOrder = CONVERT(INT, RAND() * @iEnd) WHERE V_ID = @iValue
SET @iCount = @iCount + 1
ENDSET @sSQL = 'SELECT TOP ' + CONVERT(VARCHAR(8), @N) + ' * FROM visitor ORDER BY V_RanOrder'EXECUTE(@sSQL)4.执行EXEC proc_test @N=1000,完全通过。请各位指教!
@N INT = 100
ASDECLARE @iStart INT
DECLARE @iEnd INT
DECLARE @iCount INT
DECLARE @iValue INT
DECLARE @sSQL VARCHAR(8000)SET @iCount = 1
SET @sSQL = ''/*取最小ID*/
SELECT TOP 1 @iStart = v_id FROM visitor ORDER BY v_id ASC
/*取最大ID*/
SELECT TOP 1 @iEnd = v_id FROM visitor ORDER BY v_id DESC/*一定数量的循环,保证V_RanOrder被打乱*/
WHILE @iCount < @iEnd / 5
BEGIN
/*保证iValue位于iStart和iEnd之间*/
SET @iValue = CONVERT(INT, RAND() * (@iEnd - @iStart + 1) + @iStart)
/*更新序列值*/
UPDATE Visitor SET V_RanOrder = CONVERT(INT, RAND() * @iEnd) WHERE V_ID = @iValue
SET @iCount = @iCount + 1
END/*取序列N条记录*/
SET @sSQL = 'SELECT TOP ' + CONVERT(VARCHAR(8), @N) + ' * FROM visitor ORDER BY V_RanOrder'EXECUTE(@sSQL)
你的方法是可行的,但是不适用于大容量,两个表建立联结的话速度会很慢。
不过给你10分,反正分也没有什么用,不能换成美钞。 :)