一个关于随机抽取数字的问题,请大家帮帮忙.
详情如下:
其实这是公司用来制作中秋抽奖奖劵用的.
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
) 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
