一个关于随机抽取数字的有关问题,请大家帮帮忙
一个关于随机抽取数字的问题,请大家帮帮忙.
详情如下:
其实这是公司用来制作中秋抽奖奖劵用的.
1.有从1至36个字数,
2.需制作1200张奖劵,
3.内部需字死200个人中奖,所以到时开奖号码为:10,11,20,5,27,33
开奖号码为: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
详情如下:
其实这是公司用来制作中秋抽奖奖劵用的.
1.有从1至36个字数,
2.需制作1200张奖劵,
3.内部需字死200个人中奖,所以到时开奖号码为:10,11,20,5,27,33
开奖号码为: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