双色球得一些基础分析[sql]

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

表结构为：

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

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 ]
GO

ALTER TABLE [ dbo ]. [ tbSrcData ] WITH NOCHECK ADD
CONSTRAINT [ PK_tbSrcData ] PRIMARY KEY CLUSTERED
(
[ Q ]
) ON [ PRIMARY ]
GO

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

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

-- 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
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1
set @IsNext = case when ABS( @F2 - @F3) = 1 then 1 else 0 end
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1
set @IsNext = case when ABS( @F3 - @F4) = 1 then 1 else 0 end
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1
set @IsNext = case when ABS( @F4 - @F5) = 1 then 1 else 0 end
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1
set @IsNext = case when ABS( @F5 - @F6) = 1 then 1 else 0 end
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1
set @IsNext = case when ABS( @F6 - @F7) = 1 then 1 else 0 end
if @IsNext = 1 set @ILinkCount = @ILinkCount + 1

Update #TmpLinkTable set FLINK = @ILinkCount where AutoID = @WI
set @WI = @WI + 1
end

select * from #TmpLinkTable

-- Select (Select Count(*) from #TmpLinkTable ) / (select Sum(FLink) from #TmpLinkTable) as 连数出现频率

select *,( Select Count( *) from #TmpLinkTable ) / ( select Sum(FLink) from #TmpLinkTable) as 平均多少期出现连号,
(f1 +f2 +f3 +F4 +F5 +f6 +f7) as 和
Into #TmpSumTable
from #TmpLinkTable

drop table #TmpLinkTable

select
最高期 = ( select Top 1 Q from #TmpSumTable order by 和 desc),
最高和 = ( select Top 1 和 from #TmpSumTable order by 和 desc),
最低期 = ( select Top 1 Q from #TmpSumTable order by 和 asc),
最低和 = ( select Top 1 和 from #TmpSumTable order by 和 asc)

select ( 152 - 67) / 3
select 67

select a. *,
[ 差 ] = abs( a.和 - ( Select 和 from #TmpSumTable where AutoId =(a.AutoID + 1))),
[ 最大最小差百分比 ] =
(
cast( abs( a.和 - ( Select 和 from #TmpSumTable where AutoId =(a.AutoID + 1))) as float)
/
cast(
( select Top 1 和 from #TmpSumTable order by 和 desc) -
( select Top 1 和 from #TmpSumTable order by 和 asc)
as float)
) * 100
from #TmpSumTable a order by Q ASC
-- select
-- 3 , 9 , 12 , 15 , 16 , 4 , 24,
-- 3 + 9 + 12 + 15 + 16 + 4 + 24

-- select 85 * 0.75
-- select 63.75 / 85

Drop table #TmpSumTable

本文转自suifei博客园博客，原文链接http://www.cnblogs.com/Chinasf/archive/2005/10/23/260502.html，如需转载请自行联系原作者