代码是心血来潮编写得,就象买彩票一样,为国家做贡献;
首先建立一个表,表得数据可以从 福利彩票网站上获得,自己一个一个得录入进去先;
表结构为:
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,如需转载请自行联系原作者