双色球基础分析--SQL

代码是心血来潮编写得，就象买彩票一样，为国家做贡献；
首先建立一个表，表得数据可以从福利彩票网站上获得，自己一个一个得录入进去先；

表结构为：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbSrcData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tbSrcData]

CREATE TABLE [dbo].[tbSrcData] (

[Q] [varchar] (10) NOT NULL , --期数

[F1] [int] NULL , --1号球数

[F2] [int] NULL , --2号球数

[F3] [int] NULL , --3号球数

[F4] [int] NULL , --4号球数

[F5] [int] NULL , --5号球数

[F6] [int] NULL , --6号球数

[F7] [int] NULL , --7号兰球数

[FXQT] [int] NULL --快乐星期天球数

) ON [PRIMARY]

ALTER TABLE [dbo].[tbSrcData] WITH NOCHECK ADD

CONSTRAINT [PK_tbSrcData] PRIMARY KEY CLUSTERED

(

[Q]

) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_tbSrcData] ON [dbo].[tbSrcData]([Q]) WITH IGNORE_DUP_KEY ON [PRIMARY]

运行以下代码到查询分析器内：

--select * from tbSrcData

--求单双

Select ZZ.*,6-单 as [双]

from(

select

(case when (F1 % 2) <>0 then 1 else 0 end) +

(case when (F2 %2)<>0 then 1 else 0 end) +

(case when (F3 %2)<>0 then 1 else 0 end) +

(case when (F4 %2)<>0 then 1 else 0 end) +

(case when (F5 %2)<>0 then 1 else 0 end) +

(case when (F6 %2)<>0 then 1 else 0 end) as 单

from tbSrcData A

) AS ZZ

Select ZZ.单,6-单 as [双]

into #TTT

from(

select

(case when (F1 % 2) <>0 then 1 else 0 end) +

(case when (F2 %2)<>0 then 1 else 0 end) +

(case when (F3 %2)<>0 then 1 else 0 end) +

(case when (F4 %2)<>0 then 1 else 0 end) +

(case when (F5 %2)<>0 then 1 else 0 end) +

(case when (F6 %2)<>0 then 1 else 0 end) as 单

from tbSrcData A

) AS ZZ

select Sum(单) as a,Sum(双) as b FROM #TTT

drop table #TTT

set nocount on

--求出现率最高数

declare @iCount int

declare @dnySql varchar(1024)

declare @F1 int,@F2 int,@F3 int,@F4 int,@F5 int,@F6 int,@F7 int,@Q varchar(10)

declare @WI int

declare @IsNext bit

declare @ILinkCount int

declare @blueNum int

set @iCount = 1

Create Table #TmpTable(

Num Int null

)

while @iCount<=7

begin

set @dnySql = 'Insert Into #TmpTable select F'+Convert(Varchar,@iCount)+' from tbSrcData'

Exec(@dnySql)

set @iCount = @iCount + 1

end

Select Top 6 Num as 号码,Count(*) as 出现次数, (select Count(*) from tbSrcData) / Count(*) AS 出现期数, (case when(Num % 2 <>0) then '单' else '双' end) as 类型

from #TmpTable

Group by Num

Order by Count(*) Desc

Select Num as 号码,Count(*) as 出现次数, (select Count(*) from tbSrcData) / Count(*) AS 出现期数, (case when(Num % 2 <>0) then '单' else '双' end) as 类型

from #TmpTable

Group by Num

Order by Count(*) asc

set @blueNum =0

select @blueNum = BZ.F7 from(

select Top 1 F7 from tbSrcData group by F7 order by Count(*) desc

) AS BZ

select @blueNum as '兰色号码'

Select IDENTITY(int,1,1) as AutoID,AZ.号码

Into #TmpListTable

from (

Select Top 6 Num as 号码

from #TmpTable

Group by Num

Order by Count(*) Desc

)AS AZ

order by AZ.号码 asc

Drop table #TmpTable

create table #Table(

Q varchar(10) null,f1 int null,f2 int null,f3 int null,f4 int null,f5 int null,f6 int null,f7 int null,fxqt int null

)

Insert into #Table(Q,fxqt)values('最高频率',0)

set @WI = 1

while @WI<=(Select Max(AutoID) from #TmpListTable)

begin

set @dnySql = 'Update #Table set f' + Cast(@WI as Varchar) + '=(select 号码 from #TmpListTable where AutoId = '+Cast(@WI as varchar)+')'

exec(@dnySql)

set @WI = @WI + 1

end

update #Table set f7=@blueNum

drop table #TmpListTable

select * from #Table

Select IDENTITY(int,1,1) as AutoID,*

Into #TmpLinkTable

from

(

select Q,F1,F2,F3,F4,F5,F6,F7,FXQT,0 as FLINK from tbSrcData

union all

select Q,F1,F2,F3,F4,F5,F6,F7,FXQT,0 as FLink from #Table

) as BYZ

drop table #Table

set @WI = 1

while @WI<=(Select Max(AutoID) from #TmpLinkTable)

begin

select @Q=Q,@F1=F1,@F2=F2,@F3=F3,@F4=F4,@F5=F5,@F6=F6,@F7=F7 from #TmpLinkTable where AutoID = @WI

set @IsNext = 0

set @ILinkCount = 0

set @IsNext = case when ABS(@F1-@F2)=1 then 1 else 0 end