# 一个关于随机抽取数字的有关问题,请大家帮帮忙(2)

www.MyException.Cn  网友分享于：2013-02-01  浏览：8次

) T
order by newid()

--select * from tb

go
create table tbLottery(nID int identity(1,1), n1 int, n2 int, n3 int, n4 int, n5 int) --存奖券,让奖券随机分布

insert tbLottery select n1, n3, n5, n2, n4 from tb order by newid()

select * from tbLottery

/*每次抽一张
declare @n int
set @n=1 --@n的取值为1~1200
select * from tbLottery where nId=@n
*/

drop table tbLottery
drop table tb1
drop table tb2
drop table tb

------解决方案--------------------
create table t1(num int)
insert into t1
select 10 union select 11 union select 20 union select 5 union select 27 union select 33

create table t2(num int)
declare @i int
set @i=1
while @i <=36
begin
insert into t2
select @i
set @i=@i+1
end

delete t2 where num in(select num from t1)

create table va (id int identity(0,1),num int)

--形成號碼
--1
set @i=1
while @i <=200
begin
insert into va(num)
select top 5 num
from t1 order by newid()
set @i=@i+1
end

--2
set @i=1
while @i <=150
begin
insert into va(num)
select top 4 num from t1 order by newid()
insert into va(num)
select top 1 num from t2 order by newid()
set @i=@i+1
end
--3
set @i=1
while @i <=200
begin
insert into va(num)
select top 3 num from t1 order by newid()
insert into va(num)
select top 2 num from t2 order by newid()
set @i=@i+1
end
--4
set @i=1
while @i <=200
begin
insert into va(num)
select top 2 num from t1 order by newid()
insert into va(num)
select top 3 num from t2 order by newid()
set @i=@i+1
end
--5
set @i=1
while @i <=100
begin
insert into va(num)
select top 1 num from t1 order by newid()
insert into va(num)
select top 4 num from t2 order by newid()
set @i=@i+1
end
--6
set @i=1
while @i <=350
begin
insert into va(num)
select top 5 num from t2 order by newid()
set @i=@i+1
end

--select count(*) from va
--drop table va

select (id/5)+1 as [id],
max(case when id%5 = 0 then num end) as n1,
max(case when id%5 = 1 then num end) as n2,
max(case when id%5 = 2 then num end) as n3,
max(case when id%5 = 3 then num end) as n4,
max(case when id%5 = 4 then num end) as n5
from va
group by (id/5)+1
order by (id/5)+1

drop table t1,t2,va