—————————SQL之機選大樂透，雙色球演算法———&

----------大樂透--------------------

if object_id('Front') is not nulldrop table Front gocreate table Front(id int ,Frontnumber int default 0 ,)

if object_id('Back') is not nulldrop table Backgocreate table Back(id INT,Backnumber int default 0 )

/*IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ID')    DROP INDEX IX_ID ON Front ;GOCREATE CLUSTERED INDEX IX_ID  ON Front(ID);GO 

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_BackID')    DROP INDEX IX_ID ON Back ;GOCREATE CLUSTERED INDEX IX_BackID  ON Back(ID);GO*/

--隨機插入10000條資料

DECLARE @I INTSET @I=1WHILE(@I<=10000)BEGININSERT INTO Front VALUES(@I,ABS(CHECKSUM(NEWID()))%33+1)SET @I=@I+1END

DECLARE @J INTSET @J=1WHILE(@J<=10000)BEGININSERT INTO Back VALUES(@J,ABS(CHECKSUM(NEWID()))%12+1)SET @J=@J+1END

if OBJECT_ID('SP_DALETOU') IS NOT NULLDROP PROC SP_DALETOUGOCREATE PROC SP_DALETOU(@I INT)ASBEGINDECLARE @SQL VARCHAR(MAX)--DECLARE @N1 INT--DECLARE @N2 INT--SET @N1=@I--SET @N2=@ISET @SQL='if object_id(''tempdb..#Front'') is not nulldrop table #Frontcreate table #Front(id int ,Frontnumber int default 0 ,)

if object_id(''tempdb..#Back'') is not nulldrop table #Backcreate table #Back(id int ,Backnumber int default 0 ,)

insert into #Front select *  from front  order by NEWID() insert into #Back select * from back order by NEWID()'

SELECT @SQL=@SQL+'select  top '+ltrim(@I)+' *from(select  C1 AS 前區1,  C2 AS 前區2,  C3 AS 前區3,  C4 AS 前區4,  C5 AS 前區5from(select  (ID-1)/5 AS PX,  MAX(case when ID%5=1 then Frontnumber else 0 end) as c1,  MAX(case when ID%5=2 then Frontnumber else 0 end) as c2,  MAX(case when ID%5=3 then Frontnumber else 0 end) as c3,  MAX(case when ID%5=4 then Frontnumber else 0 end) as c4,  MAX(case when ID%5=0 then Frontnumber else 0 end) as c5from  #FrontGROUP BY  (ID-1)/5  )twhere c1<>c2 and c1<>c3 and c1<>c4 and c1<>c5 and c2<>c3 and c2<>c4 and c2<>c5 and c3<>c4 and c3<>c5 and c4<>c5)t

select  top '+ltrim(@I)+' *from(select  C6 AS 後區1,  C7 AS 後區2from(select (ID-1)/2 AS PX,  MAX(case when ID%2=1 then Backnumber else 0 end) as c6,  MAX(case when ID%2=0 then Backnumber else 0 end) as c7from  #BackGROUP BY  (ID-1)/2)twhere  c6<>c7)t  '   exec(@SQL)

END

GO

EXEC SP_DALETOU 2

GO

 ---雙色球---------

if object_id('Red') is not nulldrop table Red gocreate table Red(id int ,Rednumber int default 0 ,)

if object_id('Blue') is not nulldrop table Bluegocreate table Blue(id INT,Bluenumber int default 0 )

/*IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RedID')    DROP INDEX IX_ID ON Red ;GOCREATE CLUSTERED INDEX IX_ID  ON Red(ID);GO 

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_BlueID')    DROP INDEX IX_ID ON Blue ;GOCREATE CLUSTERED INDEX IX_BackID  ON Blue(ID);GO */

--隨機插入10000條資料

DECLARE @I INTSET @I=1WHILE(@I<=10000)BEGININSERT INTO Red VALUES(@I,ABS(CHECKSUM(NEWID()))%35+1)SET @I=@I+1END

DECLARE @J INTSET @J=1WHILE(@J<=10000)BEGININSERT INTO Blue VALUES(@J,ABS(CHECKSUM(NEWID()))%16+1)SET @J=@J+1END

if OBJECT_ID('SP_DOUBLE') IS NOT NULLDROP PROC SP_DOUBLEGOCREATE PROC SP_DOUBLE(@I INT)ASBEGINDECLARE @SQL VARCHAR(MAX)--DECLARE @N1 INT--DECLARE @N2 INT--SET @N1=@I--SET @N2=@ISET @SQL='if object_id(''tempdb..#Red'') is not nulldrop table #Redcreate table #Red(id int ,Rednumber int default 0 ,)

if object_id(''tempdb..#Blue'') is not nulldrop table #Bluecreate table #Blue(id int ,Bluenumber int default 0 ,)

insert into #Red select *  from Red  order by NEWID()  insert into #Blue select * from Blue order by NEWID()'

SELECT @SQL=@SQL+'select  top '+ltrim(@I)+' *from(select  C1  as 紅球1,  C2  as 紅球2,  C3  as 紅球3,  C4  as 紅球4,  C5  as 紅球5,  C6  as 紅球6from(select  (ID-1)/6 AS PX,  MAX(case when ID%6=1 then Rednumber else 0 end) as c1,  MAX(case when ID%6=2 then Rednumber else 0 end) as c2,  MAX(case when ID%6=3 then Rednumber else 0 end) as c3,  MAX(case when ID%6=4 then Rednumber else 0 end) as c4,  MAX(case when ID%6=5 then Rednumber else 0 end) as c5,  MAX(case when ID%6=0 then Rednumber else 0 end) as c6from  #RedGROUP BY  (ID-1)/6  )twhere c1<>c2 and c1<>c3 and c1<>c4 and c1<>c5  and c1<>c6 and c2<>c3 and c2<>c4 and c2<>c5  and c2<>c6 and c3<>c4 and c3<>c5 and c3<>c6 and c4<>c5 and c4<>c6 and c5<>c6)t

select  top '+ltrim(@I)+'  Bluenumber  AS  藍球from  #Blue'   EXEC(@SQL)

END

GO

EXEC SP_DOUBLE 2

GO

純粹是無聊而作。沒有最佳化。不要笑話我。