--------- Machine selection of SQL lotto, two-color ball algorithm ---&

---------- Lotto --------------------

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*/

-- Insert 10000 data records randomly

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 front zone 1, C2 as front zone 2, C3 as front zone 3, c4 as front zone 4, C5 as front zone 5 from (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 rear Zone 1, C7 as rear Zone 2 from (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

--- Two-color ball ---------

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 */

-- Insert 10000 data records randomly

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 red ball 1, C2 as red ball 2, C3 as red ball 3, c4 as red ball 4, C5 as red ball 5, C6 as red ball 6 from (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 blue ball from # Blue' exec (@ SQL)

END

GO

EXEC SP_DOUBLE 2

GO

It is purely boring. Not optimized. Don't laugh at me.