T-SQL 模拟彩票预测

比较简单，只是模拟彩票出数字的过程，不计算单一数字的出现概率。

传统上来说，每次彩票出号的概率都是独立事件，单纯的在可选数字内随机实现即可。

本文探索的是实现简单的预测分析，包含历史开奖结果的连续事件。

举例说明：(模拟三个数字，数字区域1-10)

第一次开奖  1,2,3

第二次预测  [4-10] + [1-10 ]中按次序随机取三个数字，每随机取一个数字后都要在数字区域移除这个数字。

第二次开奖 4,5,6

第三次预测 [4-10] +[1-3]+[7-10]+[1-10] 中按次序随机取三个数字，每随机取一个数字后都要在数字区域移除这个数字。

..

..

第N次预测  在前N次开奖数字之外的其他数字+本次的数字区域 中按次序随机取三个数字，每随机取一个数字后都要在数字区域移除这个数字。

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

思路就是把历史每次开奖数字剩下的数字集合放到下一次抽奖池中进行随机

步骤如下

1.创建一张表 保存历史开奖结果。

2.将每次开奖结果以外的其他数字 放到抽奖池中 。

3.将预测本次开奖结果的全部数字 放到抽奖池中。

3.在抽奖池中逐个随机抽取数字。每轮抽取中的数字都要在抽奖池中排除。

4.输出显示结果。

代码模拟的是大乐透，分前区数字1-35，后区数字1-12，前区和后区相互独立，分开处理

  1 /******************************************************************
  2 drop table Number
  3 create table Number
  4 (
  5     ID  int identity(1,1),
  6     Ver varchar(5),
  7     Num1 int,
  8     Num2 int,
  9     Num3 int,
 10     Num4 int,
 11     Num5 int,
 12     Num6 int,
 13     Num7 int
 14 )
 15 
 16 select * from Number
 17 
 18 insert into Number
 19 select '23004','04','13','15','20','22','04','08' union all 
 20 select '23005','01','03','10','20','29','06','11' union all 
 21 select '23006','02','04','12','27','35','06','07' union all 
 22 select '23007','02','03','24','30','32','01','06' 
 23 
 24 ******************************************************************/
 25 IF(OBJECT_ID('tempdb..#Front')) IS NOT NULL 
 26 DROP TABLE #Front;
 27 CREATE TABLE #Front
 28 (
 29 Id int identity(1,1),
 30 Num int 
 31 )
 32 
 33 IF(OBJECT_ID('tempdb..#Back')) IS NOT NULL 
 34 DROP TABLE #Back;
 35 
 36 CREATE TABLE #Back
 37 (
 38 Id int identity(1,1),
 39 Num int 
 40 )
 41 
 42 DECLARE @FrontNum INT
 43 DECLARE @BackNum INT
 44 DECLARE @Rolling INT
 45 
 46 SET @Rolling = 1
 47 
 48 WHILE (@Rolling<=(SELECT COUNT(*)+1 FROM Number))
 49 BEGIN
 50 SET @FrontNum = 1
 51 
 52         WHILE(@FrontNum<=35)
 53         BEGIN 
 54             INSERT INTO #Front
 55             SELECT @FrontNum
 56         SET    @FrontNum = @FrontNum+1
 57         END
 58 SET @BackNum = 1
 59         WHILE(@BackNum<=12)
 60         BEGIN 
 61             INSERT INTO #Back
 62             SELECT @BackNum
 63         SET    @BackNum = @BackNum+1
 64         END
 65     SET    @Rolling = @Rolling+1
 66 END
 67 
 68 
 69 
 70 IF(OBJECT_ID('tempdb..#BASE')) IS NOT NULL 
 71 DROP TABLE #BASE;
 72 
 73 select 
 74  Ver
 75 ,KeyNum
 76 ,Value
 77 INTO #BASE
 78 FROM Number
 79 UNPIVOT
 80 (
 81 Value for KeyNum in ([Num1], [Num2], [Num3], [Num4], [Num5], [Num6], [Num7])
 82 ) a
 83 
 84 
 85 DECLARE Front_Cursor Cursor
 86 FOR SELECT [Value] as Num FROM #BASE WHERE RIGHT(KeyNum,1)<=5;
 87 
 88 OPEN Front_Cursor;
 89 DECLARE @Var_Front int;
 90 FETCH NEXT FROM Front_Cursor into @Var_Front
 91 WHILE (@@FETCH_STATUS=0)
 92 BEGIN 
 93     DELETE FROM #Front WHERE ID IN (SELECT top 1 id FROM #Front WHERE Num = @Var_Front order by id)
 94     FETCH NEXT FROM Front_Cursor into @Var_Front
 95 END
 96 GO
 97 CLOSE Front_Cursor;
 98 DEALLOCATE Front_Cursor;
 99 
100 
101 DECLARE Back_Cursor Cursor
102 FOR SELECT [Value] as Num FROM #BASE WHERE RIGHT(KeyNum,1)>5;
103 
104 OPEN Back_Cursor;
105 DECLARE @Var_Back int;
106 FETCH NEXT FROM Back_Cursor into @Var_Back
107 WHILE (@@FETCH_STATUS=0)
108 BEGIN 
109     DELETE FROM #Back WHERE ID IN (SELECT top 1 id FROM #Back WHERE Num = @Var_Back order by id)
110     FETCH NEXT FROM Back_Cursor into @Var_Back
111 END
112 GO
113 CLOSE Back_Cursor;
114 DEALLOCATE Back_Cursor;
115 
116 
117 
118 /***************************
119 Finial
120 **************************/
121 
122 IF(OBJECT_ID('tempdb..#Finial')) IS NOT NULL 
123 DROP TABLE #Finial;
124 CREATE TABLE #Finial
125 (
126 Id int identity(1,1),
127 Num int 
128 )
129 
130 DECLARE @Seq1 int
131 DECLARE @Num1 int
132 SET @Seq1 = 1 
133 WHILE (@Seq1<=5)
134 BEGIN
135     SET @Num1 = (SELECT TOP 1 Num from #Front order by NEWID())
136     PRINT @Num1
137     INSERT INTO  #Finial SELECT @Num1
138     DELETE FROM #Front WHERE Num = @Num1
139     SET @Seq1 = @Seq1+1
140 END
141 
142 DECLARE @Seq2 int
143 DECLARE @Num2 int
144 SET @Seq2 = 1
145 WHILE (@Seq2<=2)
146 BEGIN
147     SET @Num2 = (SELECT TOP 1 Num from #Back order by NEWID())
148     INSERT INTO #Finial SELECT @Num2
149     DELETE FROM #Back WHERE Num = @Num2
150     SET @Seq2 = @Seq2+1
151 END
152 
153 
154 IF(OBJECT_ID('tempdb..#RESULT')) IS NOT NULL 
155 DROP TABLE #RESULT;
156 
157 WITH CTE AS
158 (
159 SELECT ROW_NUMBER()over(order by Num) as Seq,Num from #Finial where id<=5 
160 UNION ALL
161 SELECT ROW_NUMBER()over(order by Num)+5 as Seq,Num from #Finial where id>5 
162 )
163 ,Final as 
164 (
165 
166 SELECT 
167  'Num'+CONVERT(varchar,Seq) as NumSeq 
168 ,Num as Value 
169 FROM CTE 
170 )
171 
172 
173 SELECT * INTO #RESULT 
174 FROM Final
175 PIVOT
176 (
177 SUM(Value) FOR NumSeq in (Num1,Num2,Num3,Num4,Num5,Num6,Num7)
178 ) a 
179 
180 SELECT * FROM #RESULT