----------大乐透--------------------

if object_id('Front') is not null
drop table Front 
go
create table Front
(
id int ,
Frontnumber int default 0 ,
)

if object_id('Back') is not null
drop table Back
go
create 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 ;
GO
CREATE 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 ;
GO
CREATE CLUSTERED INDEX IX_BackID  ON Back(ID);
GO*/

--随机插入10000条数据

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

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

if OBJECT_ID('SP_DALETOU') IS NOT NULL
DROP PROC SP_DALETOU
GO
CREATE PROC SP_DALETOU
(
@I INT
)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
--DECLARE @N1 INT
--DECLARE @N2 INT
--SET @N1=@I
--SET @N2=@I
SET @SQL='
if object_id(''tempdb..#Front'') is not null
drop table #Front
create table #Front
(
id int ,
Frontnumber int default 0 ,
)

if object_id(''tempdb..#Back'') is not null
drop table #Back
create 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 前区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 c5
from
  #Front
GROUP BY
  (ID-1)/5
  )t
where
 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 后区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 c7
from
  #Back
GROUP BY
  (ID-1)/2
)t
where
  c6

c7)t  '
   
exec(@SQL)

END

GO

EXEC SP_DALETOU 2

GO

 ---双色球---------

if object_id('Red') is not null
drop table Red 
go
create table Red
(
id int ,
Rednumber int default 0 ,
)

if object_id('Blue') is not null
drop table Blue
go
create 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 ;
GO
CREATE 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 ;
GO
CREATE CLUSTERED INDEX IX_BackID  ON Blue(ID);
GO */

--随机插入10000条数据

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

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

if OBJECT_ID('SP_DOUBLE') IS NOT NULL
DROP PROC SP_DOUBLE
GO
CREATE PROC SP_DOUBLE
(
@I INT
)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
--DECLARE @N1 INT
--DECLARE @N2 INT
--SET @N1=@I
--SET @N2=@I
SET @SQL='
if object_id(''tempdb..#Red'') is not null
drop table #Red
create table #Red
(
id int ,
Rednumber int default 0 ,
)

if object_id(''tempdb..#Blue'') is not null
drop table #Blue
create 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 红球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 c6
from
  #Red
GROUP BY
  (ID-1)/6
  )t
where
 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

纯粹是无聊而作。没有优化。不要笑话我。