---------------------------SQL之机选大乐透,双色球算法-----------------------------

 
----------大乐透--------------------
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
 
 
纯粹是无聊而作。没有优化。不要笑话我。
 
 
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值