--随机生成十个数字 保证平均值为ndeclare @t table(id int) declare @n int set @n=15insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10) insert into @t select ceiling((20-10)*rand()+10)select * from @t a union all select 15*9-sum(id)+15 from @t /* id ----------- 19 17 12 11 17 14 19 12 14 15 */
/* 补充,如果是用表里的数据,很难找到符合你平均值,又符合固定条目的数据。#3的表变量,改成临时表的话,代码可以简化扩展。 */ --随机生成m个数字 保证平均值为ndeclare @m int ;set @m=10 declare @n int ;set @n=15 declare @k int ;set @k=5 --随机数范围create table #t(id int) declare @i int;set @i=1 while @i<@m begin insert into #t select ceiling((2*@k)*rand()+@n-@k) set @i=@i+1 endselect * from #t a union all select @n*(@m-1)-sum(id)+@n from #tdrop table #t /* id ----------- 12 15 12 11 20 19 11 15 13 22 */
--建表 create table #Avg(autoid int, num int)insert into #Avg select 1,1 union all select 2,2 union all select 3,3 union all select 4,2 union all select 5,5 union all select 6,2 union all select 7,2 union all select 8,7 union all select 9,2--假如定义平均值为2,则随即抽取2条记录的结果为(这里举2条,要想10条就 top 10)select * from #avg where autoid in ( select top 2 max(autoid) from #avg group by autoid-ascii(left(newid(),1)) having avg(num)=2 )
To Maco_wang:谢谢你的帮助。 两段代码我都测试过了,第二段代码,如果把@m设置成100,即表中有100条记录,最后select出的记录也是100条,我只需要随机取10条,不知道是不是我弄错了?
这个题目很有意思,我试了一下--建演示数据 create table #t(id int identity, v int) declare @i int set @i = 1 while @i <= 1000 begin insert #t values(ceiling(rand()*100)) set @i = @i + 1 end go--OK,下面开始 declare @avg int --要取的固定的平均值 set @avg = 65declare @row int --要取的行数 set @row = 10declare @cn int --表的总行数 select @cn = count(*) from #tcreate table #m(gid int,id int,v int)again: insert #m select gid=(row_number() over(order by newid()))%(@cn/@row),* from #tselect id,v from #m where gid = (select top 1 gid from #m group by gid having count(*) = 10 and avg(v) = @avg) if @@rowcount <> 0 drop table #m else goto again/* --每次执行都不同,这里随便列其中的一次结果 id v ----------- ----------- 919 12 889 79 87 81 795 99 352 33 599 82 109 75 968 28 300 97 837 67(10 行受影响) */ 老实说,感觉效率还不是很高...
修正一下#11--演示数据同#11declare @avg int --要取的固定的平均值 set @avg = 65declare @row int --要取的行数 set @row = 8declare @cn int --表的总行数 select @cn = count(*) from #tcreate table #m(gid int,id int,v int) create table #n(id int,v int)again: insert #m select gid=(row_number() over(order by newid()))%(@cn/@row),* from #tinsert #n select id,v from #m where gid = (select top 1 gid from #m group by gid having count(*) = @row and avg(v) = @avg) if @@rowcount <> 0 begin select * from #n drop table #m drop table #n end else begin truncate table #m goto again end to 楼主: 之所以说效率不高,是因为这一段程序显得很土,不停的循环,直到找到为止。 在极端的情况下,可能要循环很久......, 当然,这取决于你表的大小、设定的平均值和行数。
--随机生成十个数字 保证平均值为ndeclare @t table(id int)
declare @n int
set @n=15insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)
insert into @t select ceiling((20-10)*rand()+10)select * from @t a union all
select 15*9-sum(id)+15 from @t
/*
id
-----------
19
17
12
11
17
14
19
12
14
15
*/
/*
补充,如果是用表里的数据,很难找到符合你平均值,又符合固定条目的数据。#3的表变量,改成临时表的话,代码可以简化扩展。
*/
--随机生成m个数字 保证平均值为ndeclare @m int ;set @m=10
declare @n int ;set @n=15
declare @k int ;set @k=5 --随机数范围create table #t(id int)
declare @i int;set @i=1
while @i<@m
begin
insert into #t select ceiling((2*@k)*rand()+@n-@k)
set @i=@i+1
endselect * from #t a union all
select @n*(@m-1)-sum(id)+@n from #tdrop table #t
/*
id
-----------
12
15
12
11
20
19
11
15
13
22
*/
create table #Avg(autoid int, num int)insert into #Avg
select 1,1
union all select 2,2
union all select 3,3
union all select 4,2
union all select 5,5
union all select 6,2
union all select 7,2
union all select 8,7
union all select 9,2--假如定义平均值为2,则随即抽取2条记录的结果为(这里举2条,要想10条就 top 10)select * from #avg where autoid in
(
select top 2 max(autoid) from #avg
group by autoid-ascii(left(newid(),1)) having avg(num)=2
)
两段代码我都测试过了,第二段代码,如果把@m设置成100,即表中有100条记录,最后select出的记录也是100条,我只需要随机取10条,不知道是不是我弄错了?
create table #t(id int identity, v int)
declare @i int
set @i = 1
while @i <= 1000
begin
insert #t values(ceiling(rand()*100))
set @i = @i + 1
end
go--OK,下面开始
declare @avg int --要取的固定的平均值
set @avg = 65declare @row int --要取的行数
set @row = 10declare @cn int --表的总行数
select @cn = count(*) from #tcreate table #m(gid int,id int,v int)again:
insert #m select gid=(row_number() over(order by newid()))%(@cn/@row),* from #tselect id,v from #m where gid = (select top 1 gid from #m group by gid having count(*) = 10 and avg(v) = @avg)
if @@rowcount <> 0
drop table #m
else
goto again/*
--每次执行都不同,这里随便列其中的一次结果
id v
----------- -----------
919 12
889 79
87 81
795 99
352 33
599 82
109 75
968 28
300 97
837 67(10 行受影响)
*/
老实说,感觉效率还不是很高...
set @avg = 65declare @row int --要取的行数
set @row = 8declare @cn int --表的总行数
select @cn = count(*) from #tcreate table #m(gid int,id int,v int)
create table #n(id int,v int)again:
insert #m select gid=(row_number() over(order by newid()))%(@cn/@row),* from #tinsert #n select id,v from #m where gid = (select top 1 gid from #m group by gid having count(*) = @row and avg(v) = @avg)
if @@rowcount <> 0
begin
select * from #n
drop table #m
drop table #n
end
else
begin
truncate table #m
goto again
end
to 楼主:
之所以说效率不高,是因为这一段程序显得很土,不停的循环,直到找到为止。
在极端的情况下,可能要循环很久......, 当然,这取决于你表的大小、设定的平均值和行数。