最近在研究彩票,呵呵,想统计一下5连号的周期,总共有11个号码,怎样来判断连号,这样的语句怎样写比较好?
示例代码:
create table #a(期号 int identity(1,1),号码1 int,号码2 int,号码3 int,号码4 int,号码5 int)
goinsert #a
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7
go想要的查询结果:
期号 号码1 号码2 号码3 号码4 号码5 距上次间隔
2 4 3 2 6 5 2
5 1 2 4 5 3 3
12 1 3 2 4 5 7
示例代码:
create table #a(期号 int identity(1,1),号码1 int,号码2 int,号码3 int,号码4 int,号码5 int)
goinsert #a
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7
go想要的查询结果:
期号 号码1 号码2 号码3 号码4 号码5 距上次间隔
2 4 3 2 6 5 2
5 1 2 4 5 3 3
12 1 3 2 4 5 7
(期号 int identity(1,1),
号码1 int,号码2 int,号码3 int,号码4 int,号码5 int)
insert into @table (号码1,号码2,号码3,号码4,号码5)
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7select * from @table where
charindex(',5,',','+
cast(号码1 as varchar(2))+','+
cast(号码2 as varchar(2))+','+
cast(号码3 as varchar(2))+','+
cast(号码4 as varchar(2))+','+
cast(号码5 as varchar(2))+',')>0/*
期号 号码1 号码2 号码3 号码4 号码5
----------- ----------- ----------- ----------- ----------- -----------
2 4 3 2 6 5
3 5 8 2 9 7
4 11 3 5 2 7
5 1 2 4 5 3
8 9 2 1 6 5
10 7 8 2 6 5
12 1 3 2 4 5
13 2 9 4 7 5
14 2 9 4 5 7
*/
goinsert #a
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7
go;with cte(id,num) as
(
select 期号,号码1 from #a union
select 期号,号码2 from #a union
select 期号,号码3 from #a union
select 期号,号码4 from #a union
select 期号,号码5 from #a
)
select a.* from #a a join (select id from cte group by id having max(num)-min(num)=count(1)-1) b
on a.期号=b.id/*
期号 号码1 号码2 号码3 号码4 号码5
----------- ----------- ----------- ----------- ----------- -----------
2 4 3 2 6 5
5 1 2 4 5 3
12 1 3 2 4 5
*/
(
select 期号,号码1 from #a union
select 期号,号码2 from #a union
select 期号,号码3 from #a union
select 期号,号码4 from #a union
select 期号,号码5 from #a
)
select a.* from #a a join (select id from cte group by id having max(num)-min(num)=4 and count(1)=5) b
on a.期号=b.id5连号
--思路:最小期号+4 等于 最大期号create table #a(期号 int identity(1,1),号码1 int,号码2 int,号码3 int,号码4 int,号码5 int)
goinsert #a
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7
goselect *
from #a as t1
where (select case
when 号码1<=号码2 and 号码1<=号码3 and 号码1<=号码4 and 号码1<=号码5 then 号码1
when 号码2<=号码1 and 号码2<=号码3 and 号码2<=号码4 and 号码2<=号码5 then 号码2
when 号码3<=号码1 and 号码3<=号码2 and 号码3<=号码4 and 号码3<=号码5 then 号码3
when 号码4<=号码1 and 号码4<=号码2 and 号码4<=号码3 and 号码4<=号码5 then 号码4
when 号码5<=号码1 and 号码5<=号码2 and 号码5<=号码3 and 号码5<=号码4 then 号码5
end
from #a as t2
where t1.期号=t2.期号)+4
=
(select case
when 号码1>=号码2 and 号码1>=号码3 and 号码1>=号码4 and 号码1>=号码5 then 号码1
when 号码2>=号码1 and 号码2>=号码3 and 号码2>=号码4 and 号码2>=号码5 then 号码2
when 号码3>=号码1 and 号码3>=号码2 and 号码3>=号码4 and 号码3>=号码5 then 号码3
when 号码4>=号码1 and 号码4>=号码2 and 号码4>=号码3 and 号码4>=号码5 then 号码4
when 号码5>=号码1 and 号码5>=号码2 and 号码5>=号码3 and 号码5>=号码4 then 号码5
end
from #a as t3
where t1.期号=t3.期号);
/**
期号 号码1 号码2 号码3 号码4 号码5
----------- ----------- ----------- ----------- ----------- -----------
2 4 3 2 6 5
5 1 2 4 5 3
12 1 3 2 4 5(3 行受影响)
**/
and 号码2<>号码3 and 号码2<>号码4 and 号码2<>号码5
and 号码3<>号码4 and 号码3<>号码5
and 号码4<>号码5
带间隔次数 前提:期号须是连接的
结果中间隔次数表示 当前条与上一条之间间隔次数
with temp as
(
select 期号,号码1,号码2,号码3,号码4,号码5
from #a as t1
where (select case
when 号码1<=号码2 and 号码1<=号码3 and 号码1<=号码4 and 号码1<=号码5 then 号码1
when 号码2<=号码1 and 号码2<=号码3 and 号码2<=号码4 and 号码2<=号码5 then 号码2
when 号码3<=号码1 and 号码3<=号码2 and 号码3<=号码4 and 号码3<=号码5 then 号码3
when 号码4<=号码1 and 号码4<=号码2 and 号码4<=号码3 and 号码4<=号码5 then 号码4
when 号码5<=号码1 and 号码5<=号码2 and 号码5<=号码3 and 号码5<=号码4 then 号码5
end
from #a as t2
where t1.期号=t2.期号)+4
=
(select case
when 号码1>=号码2 and 号码1>=号码3 and 号码1>=号码4 and 号码1>=号码5 then 号码1
when 号码2>=号码1 and 号码2>=号码3 and 号码2>=号码4 and 号码2>=号码5 then 号码2
when 号码3>=号码1 and 号码3>=号码2 and 号码3>=号码4 and 号码3>=号码5 then 号码3
when 号码4>=号码1 and 号码4>=号码2 and 号码4>=号码3 and 号码4>=号码5 then 号码4
when 号码5>=号码1 and 号码5>=号码2 and 号码5>=号码3 and 号码5>=号码4 then 号码5
end
from #a as t3
where t1.期号=t3.期号)
)
select 期号,号码1,号码2,号码3,号码4,号码5,
abs(coalesce((select max(期号)
from temp as p2
where p2.期号<p1.期号),0)-期号) as 间隔
from temp as p1
/**
期号 号码1 号码2 号码3 号码4 号码5 间隔
----------- ----------- ----------- ----------- ----------- ----------- -----------
2 4 3 2 6 5 2
5 1 2 4 5 3 3
12 1 3 2 4 5 7(3 行受影响)**/
把 where条件中coalesce(...,0)换为coalesce(...,1)
仅表示是从第期开始的
#11代码可以简化的,#13一样,你再测一下速度select * from #a where
case
when 号码1<=号码2 and 号码1<=号码3 and 号码1<=号码4 and 号码1<=号码5 then 号码1
when 号码2<=号码1 and 号码2<=号码3 and 号码2<=号码4 and 号码2<=号码5 then 号码2
when 号码3<=号码1 and 号码3<=号码2 and 号码3<=号码4 and 号码3<=号码5 then 号码3
when 号码4<=号码1 and 号码4<=号码2 and 号码4<=号码3 and 号码4<=号码5 then 号码4
when 号码5<=号码1 and 号码5<=号码2 and 号码5<=号码3 and 号码5<=号码4 then 号码5
end + 4 =
case
when 号码1>=号码2 and 号码1>=号码3 and 号码1>=号码4 and 号码1>=号码5 then 号码1
when 号码2>=号码1 and 号码2>=号码3 and 号码2>=号码4 and 号码2>=号码5 then 号码2
when 号码3>=号码1 and 号码3>=号码2 and 号码3>=号码4 and 号码3>=号码5 then 号码3
when 号码4>=号码1 and 号码4>=号码2 and 号码4>=号码3 and 号码4>=号码5 then 号码4
when 号码5>=号码1 and 号码5>=号码2 and 号码5>=号码3 and 号码5>=号码4 then 号码5
end
--号码1-5有相同的情况要加以下条件
and 号码1<>号码2 and 号码1<>号码3 and 号码1<>号码4 and 号码1<>号码5
and 号码2<>号码3 and 号码2<>号码4 and 号码2<>号码5
and 号码3<>号码4 and 号码3<>号码5
and 号码4<>号码5
那你先就说吧,5连号到底会不会有重复的数据出现?
goinsert #a
select 1,3,2,6,7
union all
select 4,3,2,6,5
union all
select 5,8,2,9,7
union all
select 11,3,5,2,7
union all
select 1,2,4,5,3
union all
select 10,3,2,6,7
union all
select 6,3,2,8,7
union all
select 9,2,1,6,5
union all
select 9,6,2,8,7
union all
select 7,8,2,6,5
union all
select 9,7,2,6,4
union all
select 1,3,2,4,5
union all
select 2,9,4,7,5
union all
select 2,9,4,5,7
union all
select 4,3,1,6,7
gowith c_r(期号,号码)
as
(
select 期号,号码1 from #a union
select 期号,号码2 from #a union
select 期号,号码3 from #a union
select 期号,号码4 from #a union
select 期号,号码5 from #a
)
,
c_r1 as
(
select a.*
from #a a inner join
(select 期号 from c_r group by 期号 having max(号码)-min(号码)+1=count(*)) b on a.期号=b.期号
)select *,距上次间隔=期号-isnull((select max(期号) from c_r1 where 期号<a.期号),0)
from c_r1 adrop table #a