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

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

1.有从1至36个字数,

2.需制作1200张奖劵,

3.内部需字死200个人中奖,所以到时开奖号码为:10,11,20,5,27,33

------------------------------------
350人一个数字都不中,---> ５个数字全部从非开奖号码中选，
100人中1个数字,---> ４个数字全部从非开奖号码中选，１个数字从开奖号码中选
200人中2个数字,---> ３个数字全部从非开奖号码中选，１个数字从开奖号码中选
200人中3个数字,---> ２个数字全部从非开奖号码中选，１个数字从开奖号码中选
150人中4个数字,---> １个数字全部从非开奖号码中选，１个数字从开奖号码中选
200人中5个数字.---> ５个数字全部从开奖号码中选

------解决方案--------------------
create table tb1(nId int)--存中奖号码
go
create table tb2(nId int)--存非中奖号码
go

insert tb1 select 10
union all select 11
union all select 20
union all select 5
union all select 27
union all select 33

declare @i int
set @i=1
while @i <=36
begin
insert tb2 select @i
set @i=@i+1
end
delete tb2 where nId in (select nID from tb1)

go
create table tb(nID int identity(1,1), n1 int, n2 int, n3 int, n4 int, n5 int) --存奖券

insert tb select TOP 350 n1, n2, n3, n4, n5 from --插入350张一个数字也不中的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb2 T1, tb2 T2, tb2 T3, tb2 T4, tb2 T5
where T1.nId <> T2.nId and T1.nId <> T3.nId and T1.nId <> T4.nId and T1.nId <> T5.nId
and T2.nId <> T3.nId and T2.nId <> T4.nId and T2.nId <> T5.nId
and T3.nId <> T4.nId and T3.nId <> T5.nId
and T4.nId <> T5.nId
) T
order by newid()

insert tb select TOP 100 n1, n2, n3, n4, n5 from --插入100张中1个数字的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb2 T1, tb2 T2, tb2 T3, tb2 T4, tb1 T5
where T1.nId <> T2.nId and T1.nId <> T3.nId and T1.nId <> T4.nId
and T2.nId <> T3.nId and T2.nId <> T4.nId
and T3.nId <> T4.nId
) T
order by newid()

insert tb select TOP 200 n1, n2, n3, n4, n5 from --插入200张中2个数字的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb2 T1, tb2 T2, tb2 T3, tb1 T4, tb1 T5
where T1.nId <> T2.nId and T1.nId <> T3.nId
and T2.nId <> T3.nId
and T4.nId <> T5.nId
) T
order by newid()

insert tb select TOP 200 n1, n2, n3, n4, n5 from --插入200张中3个数字的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb2 T1, tb2 T2, tb1 T3, tb1 T4, tb1 T5
where T1.nId <> T2.nId
and T3.nId <> T4.nId and T3.nId <> T5.nId
and T4.nId <> T5.nId
) T
order by newid()

insert tb select TOP 150 n1, n2, n3, n4, n5 from --插入150张中4个数字的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb2 T1, tb1 T2, tb1 T3, tb1 T4, tb1 T5
where T2.nId <> T3.nId and T2.nId <> T4.nId and T2.nId <> T5.nId
and T3.nId <> T4.nId and T3.nId <> T5.nId
and T4.nId <> T5.nId
) T
order by newid()

insert tb select TOP 200 n1, n2, n3, n4, n5 from --插入200张中5个数字的
(
select TOP 10000 T1.nId n1, T2.nId n2, T3.nId n3, T4.nId n4, T5.nId n5
from tb1 T1, tb1 T2, tb1 T3, tb1 T4, tb1 T5
where T1.nId <> T2.nId and T1.nId <> T3.nId and T1.nId <> T4.nId and T1.nId <> T5.nId
and T2.nId <> T3.nId and T2.nId <> T4.nId and T2.nId <> T5.nId
and T3.nId <> T4.nId and T3.nId <> T5.nId
and T4.nId <> T5.nId