34,594
社区成员
发帖
与我相关
我的任务
分享
--创建存储过程:
if object_id('GetNext','p') is not null
drop proc GetNext
go
create proc GetNext
@grpSize int=1, --依据组的数量 [1,8000]
@index int -- 取第几位 [1,5]
as
set nocount on
--参数检查
if @grpSize<1 or @grpSize>8000 or @index<1 or @index>5
begin
print 'para error.Input again:'
return
end
--整理结果集
select id=identity(int,1,1),dt,num,keynum=substring(num,@index,1)
into #1
from tb
order by dt
--提取依据数据(组)
select * into #2
from (select top(@grpSize) * from #1 order by id desc) t
order by id
--使用CTE进行匹配并得到预测数字
declare @s varchar(8000),@minID int,@minKey varchar(1)
select @s=isnull(@s,'')+keynum from #2
select top 1 @minID=ID,@minKey=keynum from #2 order by id
;with szx as
(
select *,topid=id,cnt=1 from #1 where keynum=@minKey and id<@minID
union all
select b.*,a.topid,a.cnt+1
from szx a join #1 b
on a.id=b.id-1 and a.cnt<@grpSize
and b.keynum=substring(@s,a.cnt+1,1)
)
select a.keynum as NextNumber from #1 a
join (select distinct topid from szx) b
on a.id=b.topid+@grpSize
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([dt] varchar(11),[num] varchar(5))
insert [tb]
select '20070401-01','58105' union all
select '20070401-02','48338' union all
select '20070401-03','30353' union all
select '20070401-04','24579' union all
select '20070401-05','13924' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
go
--select * from [tb]
--创建存储过程(仅限sql2005):
if object_id('GetNext','p') is not null
drop proc GetNext
go
create proc GetNext
@grpSize int=1, --依据组的数量 [1,8000]
@index int -- 取第几位 [1,5]
as
set nocount on
--参数检查
if @grpSize<1 or @grpSize>8000 or @index<1 or @index>5
begin
print 'para error.Input again:'
return
end
--整理结果集
select id=identity(int,1,1),dt,num,keynum=substring(num,@index,1)
into #1
from tb
order by dt
--提取依据数据(组)
select * into #2
from (select top(@grpSize) * from #1 order by id desc) t
order by id
--使用CTE进行匹配并得到预测数字
declare @s varchar(8000),@minID int,@minKey varchar(1)
select @s=isnull(@s,'')+keynum from #2
select top 1 @minID=ID,@minKey=keynum from #2 order by id
;with szx as
(
select *,topid=id,cnt=1 from #1 where keynum=@minKey and id<@minID
union all
select b.*,a.topid,a.cnt+1
from szx a join #1 b
on a.id=b.id-1 and a.cnt<2
and b.keynum=substring(@s,a.cnt+1,1)
)
select a.keynum as NextNumber from #1 a
join (select distinct topid from szx) b
on a.id=b.topid+@grpSize
go
--测试结果:
exec GetNext @grpSize=1,@index=5
/*
NextNumber
----------
9
*/
exec GetNext 2,4
/*
NextNumber
----------
1
*/
declare @ss varchar(20)
declare @centers varchar(2000)
set @centers=''
declare sql_cursor cursor
for
select right(中奖号码,1) from 开奖情况
open sql_cursor
fetch next from sql_cursor into @ss
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
set @centers=@centers+@ss;
fetch next from sql_cursor into @ss
end
end
close sql_cursor
deallocate sql_cursor;
select right(@centers,3) '最近连续三期的个位'
select substring(@centers,charindex(right(@centers,3),@centers)+3,1) '上次出现的下一期'
/*
最近连续三期的个位
---------
204
(1 row(s) affected)
上次出现的下一期
--------
6
*/
declare @ss varchar(20)
declare @centers varchar(2000)
set @centers=''
declare sql_cursor cursor
for
select right(中奖号码,1) from 开奖情况
open sql_cursor
fetch next from sql_cursor into @ss
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
set @centers=@centers+@ss;
fetch next from sql_cursor into @ss
end
end
close sql_cursor
deallocate sql_cursor;
select right(@centers,3) '最近连续三期的个位'
select 开奖日期,right(中奖号码,1) as 个位号码 from (
select row_number() over (order by 开奖日期) as row ,*
from 开奖情况)
bb where bb.row=(select charindex(right(@centers,3),@centers)+3)
/*
最近连续三期的个位
---------
204
(1 row(s) affected)
开奖日期 个位号码
----------- ----
20070401-05 6
(1 row(s) affected)
*/
create table 开奖情况(开奖日期 varchar(11),中奖号码 varchar(20))
insert into 开奖情况
select '20070401-02','48332' union all
select '20070401-03','30350' union all
select '20070401-04','24574' union all
select '20070401-05','13926' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
select * from 开奖情况
create table 开奖情况(开奖日期 varchar(11),中奖号码 varchar(20))
insert into 开奖情况
select '20070401-02','48332' union all
select '20070401-03','30350' union all
select '20070401-04','24574' union all
select '20070401-05','13926' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
select * from 开奖情况
declare @ss varchar(20)
declare @centers varchar(2000)
set @centers=''
declare sql_cursor cursor
for
select right(中奖号码,1) from 开奖情况
open sql_cursor
fetch next from sql_cursor into @ss
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @ss;
set @centers=@centers+@ss;
select @centers
fetch next from sql_cursor into @ss
end
end
close sql_cursor
deallocate sql_cursor;
/*结果
204697204
*/
declare @table table (开奖日期 varchar(11),中奖号码 varchar(20))
insert into @table
select '20070401-01','58105' union all
select '20070401-02','48338' union all
select '20070401-03','30353' union all
select '20070401-04','24579' union all
select '20070401-05','13924' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
declare @最新期号 varchar(20)
declare @十位 varchar(6)
set @十位=(select top 1 substring(中奖号码,4,1) from @table order by 开奖日期 desc)
set @最新期号=(select top 1 开奖日期 from @table order by 开奖日期)
select @十位 '最近一期的十位'
--通过十位的0找到第七期的十位1
select top 1 substring(中奖号码,4,1) as '上一期的十位' from @table
where 开奖日期=
(
select top 1 substring(开奖日期,1,10)+cast(cast(substring(开奖日期,11,2) as int)+1 as varchar(8))
from @table
where substring(中奖号码,4,1)=@十位 and 开奖日期<>@最新期号
) order by 开奖日期
/*
最近一期的十位
-------
0
上一期的十位
------
1
*/
declare @table table (开奖日期 varchar(11),中奖号码 varchar(20))
insert into @table
select '20070401-01','58105' union all
select '20070401-02','48338' union all
select '20070401-03','30353' union all
select '20070401-04','24579' union all
select '20070401-05','13924' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
declare @个位 varchar(6)
set @个位=(select top 1 right(中奖号码,1) from @table order by 开奖日期 desc)
select @个位 '最近一期的个位'
--通过个位的4找到第六期的个位9
select top 1 right(中奖号码,1) as '上一期的个位' from @table
where 开奖日期=
(
select top 1 substring(开奖日期,1,10)+cast(cast(substring(开奖日期,11,2) as int)+1 as varchar(8))
from @table
where right(中奖号码,1)=@个位
)
/*
最近一期的个位
-------
4
(1 row(s) affected)
上一期的个位
------
9
*/
declare @table table (开奖日期 varchar(11),中奖号码 varchar(20))
insert into @table
select '20070401-01','58105' union all
select '20070401-02','48338' union all
select '20070401-03','30353' union all
select '20070401-04','24579' union all
select '20070401-05','13924' union all
select '20070401-06','52309' union all
select '20070401-07','48517' union all
select '20070401-08','38172' union all
select '20070401-09','20320' union all
select '20070401-10','14304'
--所有记录
select * from @table
--最新一条记录
select top 1 * from @table order by 开奖日期 desc
--以个位查询
declare @个位 varchar(6)
set @个位=(select top 1 right(中奖号码,1) from @table order by 开奖日期 desc)
select @个位 '个位'
--找到第五期
select top 1 * from @table
where right(中奖号码,1)=@个位
/*
开奖日期 中奖号码
----------- --------------------
20070401-01 58105
20070401-02 48338
20070401-03 30353
20070401-04 24579
20070401-05 13924
20070401-06 52309
20070401-07 48517
20070401-08 38172
20070401-09 20320
20070401-10 14304
开奖日期 中奖号码
----------- --------------------
20070401-10 14304
个位
------
4
开奖日期 中奖号码
----------- --------------------
20070401-05 13924
*/