我平时喜欢买上几注大乐透彩票,我用固定方案过滤后发现投单中4个号,中3个号,中2个号注数比较稳定,现有个想法就是把投单中符合中4个号,中3个号,中2个号注数提取出来。步骤如下:
1:先把投单导入“投单”表
投单表
ID int
号码 char(20)
例:
ID 号码
1 01 07 31 32 35 01 02 --号码前5个是平码,后2个是特码。只需提取前5中出注数
2 01 07 31 33 35 01 02
3 01 07 31 34 35 01 02
4 01 08 11 19 22 01 02
5 01 08 11 19 25 01 02
6 01 08 11 19 29 01 02我写了一个SQL存储过程,但速度过慢,请高手指点高效SQL语句。谢谢
CREATE PROCEDURE 提奖 -----所有数据重新统计更新
@mix4 int, --至少中4个号注数
@max4 int, --至多中4个号注数
@mix3 int, --至少中3个号注数
@max3 int, --至多中3个号注数
@mix2 int, --至少中2个号注数
@max2 int --至多中2个号注数
AS
IF OBJECT_ID('提奖投单') IS NOT NULL DROP TABLE 提奖投单 --删除原来“提奖投单”表
create TABLE 提奖投单 ( ID int,HM char(20),中4 int, 中3 int, 中2 int) --创建新“提奖投单”表
declare @ID int --投单当前ID变量
declare @HM char(20) --投单当前号码变量
declare @cnt4 int , @cnt3 int , @cnt2 int --投单当前号码中4,中3,中2变量
declare tmp cursor for select * from 投单
open tmp
fetch next from tmp into @ID,@HM
while @@fetch_status=0
begin
select @cnt4=sum(中4) ,@cnt3=sum(中3),@cnt2=sum(中2) from( SELECT
CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=4 THEN 1 ELSE 0 END AS 中4
, CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=3 THEN 1 ELSE 0 END AS 中3
, CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=2 THEN 1 ELSE 0 END AS 中2
FROM 投单 )a IF @cnt4>@mix4 and @cnt4<@max4 and @cnt3>@mix3 and @cnt3<@max3 and @cnt2>@mix2 and @cnt2<@max2
insert 提奖投单 VALUES( @ID,@HM, @cnt4, @cnt3, @cnt2 ) fetch next from tmp into @ID,@HM
end
close tmp
deallocate tmp
1:先把投单导入“投单”表
投单表
ID int
号码 char(20)
例:
ID 号码
1 01 07 31 32 35 01 02 --号码前5个是平码,后2个是特码。只需提取前5中出注数
2 01 07 31 33 35 01 02
3 01 07 31 34 35 01 02
4 01 08 11 19 22 01 02
5 01 08 11 19 25 01 02
6 01 08 11 19 29 01 02我写了一个SQL存储过程,但速度过慢,请高手指点高效SQL语句。谢谢
CREATE PROCEDURE 提奖 -----所有数据重新统计更新
@mix4 int, --至少中4个号注数
@max4 int, --至多中4个号注数
@mix3 int, --至少中3个号注数
@max3 int, --至多中3个号注数
@mix2 int, --至少中2个号注数
@max2 int --至多中2个号注数
AS
IF OBJECT_ID('提奖投单') IS NOT NULL DROP TABLE 提奖投单 --删除原来“提奖投单”表
create TABLE 提奖投单 ( ID int,HM char(20),中4 int, 中3 int, 中2 int) --创建新“提奖投单”表
declare @ID int --投单当前ID变量
declare @HM char(20) --投单当前号码变量
declare @cnt4 int , @cnt3 int , @cnt2 int --投单当前号码中4,中3,中2变量
declare tmp cursor for select * from 投单
open tmp
fetch next from tmp into @ID,@HM
while @@fetch_status=0
begin
select @cnt4=sum(中4) ,@cnt3=sum(中3),@cnt2=sum(中2) from( SELECT
CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=4 THEN 1 ELSE 0 END AS 中4
, CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=3 THEN 1 ELSE 0 END AS 中3
, CASE WHEN(CASE WHEN CHARINDEX(SUBSTRING(HM,1,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,4,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,7,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,10,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(SUBSTRING(HM,13,2),SUBSTRING(@HM,1,14)) > 0 THEN 1 ELSE 0 END)=2 THEN 1 ELSE 0 END AS 中2
FROM 投单 )a IF @cnt4>@mix4 and @cnt4<@max4 and @cnt3>@mix3 and @cnt3<@max3 and @cnt2>@mix2 and @cnt2<@max2
insert 提奖投单 VALUES( @ID,@HM, @cnt4, @cnt3, @cnt2 ) fetch next from tmp into @ID,@HM
end
close tmp
deallocate tmp
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货