/* Limpire:建表脚本 */create table numWin(Win varchar(2)) insert numWin select '23' union all select '07' union all select '11' union all select '32' union all select '09' union all select '17' gocreate table numLost(Lost varchar(2)) select top 36 id=identity(int,1,1) into #temp from sysobjects,syscolumns insert numLost select * from #temp where id not in (select Win from numWin) drop table #temp update numLost set Lost='0'+Lost where len(Lost)=1 gocreate table WinSn(Start int, Step int) insert WinSn select 1,7 union all --左上右下斜对角线:1,8,15,22,29,36 select 1,6 union all --第1列 select 2,6 union all --第2列 select 3,6 union all --第3列 select 4,6 union all --第4列 select 5,6 union all --第5列 select 6,6 union all --第6列 select 6,5 union all --左下右上斜对角线 select 1,1 union all --第1行 select 7,1 union all --第2行 select 13,1 union all --第3行 select 19,1 union all --第4行 select 25,1 union all --第5行 select 31,1 --第6行 go
--获奖序列存储过程 create procedure pWin @print varchar(8000) output as set nocount ondeclare @numWin table(ID int, Win varchar(2)) insert @numWin select NULL,Win from numWin order by newid() declare @Start int, @Step int,@Win varchar(2) select top 1 @Start=Start,@Step=Step from WinSn order by newid() update @numWin set ID=@Start-@Step,@Start=@Start+@Stepselect top 36 SN=identity(int,1,1),ID=cast(NULL as int),num=cast(NULL as varchar(2)) into #number from syscolumns update a set a.ID=b.ID,a.num=b.Win from #number a join @numWin b on a.SN=b.ID update a set ID=(select count(*) from #number where SN<=a.SN and ID is null) from #number a where a.ID is nulldeclare @numLost table(ID int identity(1,1), Lost varchar(2)) insert @numLost(Lost) select Lost from numLost order by newid() update a set a.num=b.Lost from #number a join @numLost b on a.ID=b.ID where a.num is nullset @Start=0 select @print=coalesce(@print+case when @Start%6=0 then char(10) else ' ' end,'')+num,@Start=@Start+1 from #number order by SNset nocount off go
--非获奖序列存储过程 create procedure pLost @print varchar(8000) output as set nocount ondeclare @WinSn varchar(250),@SN varchar(8000),@ID int set @WinSn='1,8,15,22,29,36;1,7,13,19,25,31;2,8,14,20,26,32;3,9,15,21,27,33;4,10,16,22,28,34;5,11,17,23,29,35;6,12,18,24,30,36;6,11,16,21,26,31;1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30;31,32,33,34,35,36;'create table #number(SN int identity(1,1),num varchar(2))set @SN='1,8,15,22,29,36' while charindex(@SN+';',@WinSN)>0 begin truncate table #number insert #number(num) select * from ( select * from numWin union all select * from numLost) a order by newid() set @SN=null select @SN=coalesce(@SN+',','')+cast(SN as varchar) from #number where num in (select Win from numWin) order by SN endset @ID=0 select @print=coalesce(@print+case when @ID%6=0 then char(10) else ' ' end,'')+num,@ID=@ID+1 from #number order by SNdrop table #numberset nocount off go
--奖卷表 create table number(ID int identity(1,1),number varchar(120)) go set nocount ondeclare @print varchar(120),@i int--获奖序列--number.ID between 1 and 200 set @i=1 while @i<=200 begin set @print=null exec pWin @print output if exists (select 1 from number where number=@print) set @i=@i-1 else insert number select @print set @i=@i+1 end --非获奖序列--number.ID between 201 and 1200 set @i=1 while @i<=1000 begin set @print=null exec pLost @print output if exists (select 1 from number where number=@print) set @i=@i-1 else insert number select @print set @i=@i+1 endset nocount off go
/* 当然可以 */ --获奖序列存储过程 alter procedure pWin--...(其它略)declare @numWin table(ID int, Win varchar(2)) --insert @numWin select NULL,Win from numWin order by newid() --上面从 numWin 表随机顺序插入,如果要固定位置,numWin 表就没必要了。 --下面直接按需要的顺序插入,若顺序不对,调整一下 insert @numWin select null, '23' union all select null, '07' union all select null, '11' union all select null, '32' union all select null, '09' union all select null, '17'--...(其它略)go
Limpire:建表脚本
*/create table numWin(Win varchar(2))
insert numWin
select '23' union all
select '07' union all
select '11' union all
select '32' union all
select '09' union all
select '17'
gocreate table numLost(Lost varchar(2))
select top 36 id=identity(int,1,1) into #temp from sysobjects,syscolumns
insert numLost select * from #temp where id not in (select Win from numWin)
drop table #temp
update numLost set Lost='0'+Lost where len(Lost)=1
gocreate table WinSn(Start int, Step int)
insert WinSn
select 1,7 union all --左上右下斜对角线:1,8,15,22,29,36
select 1,6 union all --第1列
select 2,6 union all --第2列
select 3,6 union all --第3列
select 4,6 union all --第4列
select 5,6 union all --第5列
select 6,6 union all --第6列
select 6,5 union all --左下右上斜对角线
select 1,1 union all --第1行
select 7,1 union all --第2行
select 13,1 union all --第3行
select 19,1 union all --第4行
select 25,1 union all --第5行
select 31,1 --第6行
go
create procedure pWin
@print varchar(8000) output
as
set nocount ondeclare @numWin table(ID int, Win varchar(2))
insert @numWin select NULL,Win from numWin order by newid()
declare @Start int, @Step int,@Win varchar(2)
select top 1 @Start=Start,@Step=Step from WinSn order by newid()
update @numWin set ID=@Start-@Step,@Start=@Start+@Stepselect top 36 SN=identity(int,1,1),ID=cast(NULL as int),num=cast(NULL as varchar(2)) into #number from syscolumns
update a set a.ID=b.ID,a.num=b.Win from #number a join @numWin b on a.SN=b.ID
update a set ID=(select count(*) from #number where SN<=a.SN and ID is null) from #number a where a.ID is nulldeclare @numLost table(ID int identity(1,1), Lost varchar(2))
insert @numLost(Lost) select Lost from numLost order by newid()
update a set a.num=b.Lost from #number a join @numLost b on a.ID=b.ID where a.num is nullset @Start=0
select @print=coalesce(@print+case when @Start%6=0 then char(10) else ' ' end,'')+num,@Start=@Start+1 from #number order by SNset nocount off
go
create procedure pLost
@print varchar(8000) output
as
set nocount ondeclare @WinSn varchar(250),@SN varchar(8000),@ID int
set @WinSn='1,8,15,22,29,36;1,7,13,19,25,31;2,8,14,20,26,32;3,9,15,21,27,33;4,10,16,22,28,34;5,11,17,23,29,35;6,12,18,24,30,36;6,11,16,21,26,31;1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30;31,32,33,34,35,36;'create table #number(SN int identity(1,1),num varchar(2))set @SN='1,8,15,22,29,36'
while charindex(@SN+';',@WinSN)>0
begin
truncate table #number
insert #number(num)
select * from (
select * from numWin union all
select * from numLost) a
order by newid()
set @SN=null
select @SN=coalesce(@SN+',','')+cast(SN as varchar) from #number where num in (select Win from numWin) order by SN
endset @ID=0
select @print=coalesce(@print+case when @ID%6=0 then char(10) else ' ' end,'')+num,@ID=@ID+1 from #number order by SNdrop table #numberset nocount off
go
create table number(ID int identity(1,1),number varchar(120))
go
set nocount ondeclare @print varchar(120),@i int--获奖序列--number.ID between 1 and 200
set @i=1
while @i<=200
begin
set @print=null
exec pWin @print output
if exists (select 1 from number where number=@print) set @i=@i-1
else insert number select @print
set @i=@i+1
end
--非获奖序列--number.ID between 201 and 1200
set @i=1
while @i<=1000
begin
set @print=null
exec pLost @print output
if exists (select 1 from number where number=@print) set @i=@i-1
else insert number select @print
set @i=@i+1
endset nocount off
go
当然可以
*/
--获奖序列存储过程
alter procedure pWin--...(其它略)declare @numWin table(ID int, Win varchar(2))
--insert @numWin select NULL,Win from numWin order by newid()
--上面从 numWin 表随机顺序插入,如果要固定位置,numWin 表就没必要了。
--下面直接按需要的顺序插入,若顺序不对,调整一下
insert @numWin
select null, '23' union all
select null, '07' union all
select null, '11' union all
select null, '32' union all
select null, '09' union all
select null, '17'--...(其它略)go
在此,感谢Limpire的支持.谢谢!!!万分感谢!