27,579
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(
select number+1 c from master..spt_values where type='P' and number<33
)
, t2 as
(
select a.c c1, b.c c2, c.c c3, d.c c4, e.c c5, f.c c6
from t1 a
inner join t1 b on a.c<b.c
inner join t1 c on b.c<c.c
inner join t1 d on c.c<d.c
inner join t1 e on d.c<e.c
inner join t1 f on e.c<f.c
)
select * from t2
where
(c1+1=c2 or c2+1=c3 or c3+1=c4 or c4+1=c5 or c5+1=c6)
and
(
c1+c2=c3 or c1+c2=c4 or c1+c2=c5 or c1+c2=c6
or c1+c3=c4 or c1+c3=c5 or c1+c3=c6
or c1+c4=c5 or c1+c4=c6
or c1+c5=c6
or c2+c3=c4 or c2+c3=c5 or c2+c3=c6
or c2+c4=c5 or c2+c4=c6
or c2+c5=c6
or c3+c4=c5 or c3+c4=c6
or c3+c5=c6
or c4+c5=c6
)
declare @maxN tinyint =33,@L tinyint =6
declare @t0 table(n tinyint primary key,n2 varchar(2))
insert @t0 select number n,n2=right('0'+cast(number as varchar(2)),2) from master..spt_values where type='p' and number between 1 and @maxN
;with
t0 as
(select * from @t0)
,t1 as
(select 1 L,n,P=cast(n2+',' as varchar(18)),S=0
from t0
where n<=@maxN/2 and n<=@maxN-(@L*2-3)
union all
select L=L+1,t0.n,P=cast(P+n2+',' as varchar(18)),S=(case when S>0 then S when t1.n=t0.n-1 then L else 0 end)
from t1
inner join t0 on t0.n >t1.n+sign(S) and t0.n<=case when L=@L-1 and S=0 then t1.n+1 when L=@L-1 then @maxN else @maxN-(@L-L)*2+3-sign(S) end and L<@L
)
select a.P,S from t1 a where L=@L and S<=@L-2
and charindex(right('0'+cast(cast(substring(P,S*3-2,2)as tinyint)+cast(substring(P,S*3+1,2)as tinyint)as varchar(2)),2),substring(P,S*3+4,12))>0
order by P,S
declare @maxN int =33,@L int =6
declare @t0 table(n int primary key,n2 varchar(2))
insert @t0 select number n,n2=right('0'+cast(number as varchar(2)),2) from master..spt_values where type='p' and number between 1 and @maxN
;with
t0 as
(select * from @t0)
,t1 as
(select 1 L,n,P=cast(n2+',' as varchar(18)),S=0
from t0
where n<=@maxN/2 and n<=@maxN-(@L*2-3)
union all
select L=L+1,t0.n,P=cast(P+n2+',' as varchar(18)),S=(case when S>0 then S when t1.n=t0.n-1 then L else 0 end)
from t1
inner join t0 on t0.n >t1.n+S and t0.n<=case when L=@L-1 and S=0 then t1.n+1 when L=@L-1 then @maxN else @maxN-(@L-L)*2+3-sign(S) end and L<@L
)
select a.P,S from t1 a where L=@L and S<=@L-2
and charindex(right('0'+cast(cast(substring(P,S*3-2,2)as tinyint)+cast(substring(P,S*3+1,2)as tinyint)as varchar(2)),2),substring(P,S*3+4,12))>0
order by P,S