22,207
社区成员
发帖
与我相关
我的任务
分享
-->开奖数据
if object_id('tempdb..#History') is not null drop table #History
go
CREATE TABLE #History([序号][int],[NoText]varchar(20),[NO1]int,[NO2]int,[NO3]int,[NO4]int,[NO5]int,[NO6]int)
insert into #History
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
select *from #History
序号 NoText NO1 NO2 NO3 NO4 NO5 NO6
1 09 14 15 20 26 32 9 14 15 20 26 32 ---分析特点No1+20=No2+NO3 and No4+NO6=2*NO5 and SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1)=NO1
2 14 17 25 27 28 30 14 17 25 27 28 30 ---分析特点 No2+No3=No1+NO5 and No5=2*NO1 and NoText like '%7%'
3 13 20 22 26 28 31 13 20 22 26 28 31 ---分析特点 No3+No4=No2+NO5 and No4=2*NO1 但是 '13,31'就不知道如何用代码表达这样的【互为倒序数】
4 02 08 09 14 28 30 2 8 9 14 28 30 ---分析特点 (No5=2*No4)
5 06 07 16 18 29 32 6 7 16 18 29 32 ---分析特点 No1+10=No3 and (No4=3*No1) and (No6=2*No3)
6 06 07 16 18 23 32 6 7 16 18 23 32 ---分析特点 二连号06 07,倒序数23 32,同尾数06 16就不知道如何用代码表达?
7 05 08 15 17 25 33 5 8 15 17 25 33 ---分析特点 同尾数05 15 25,倒序数33就不知道如何用代码表达?
8 05 08 11 12 18 28 5 8 11 12 18 28 ---分析特点 同尾数08 18 28,倒序数11,二连号11 12,就不知道如何用代码表达?
9 09 19 21 22 27 29 9 19 21 22 27 29 ---分析特点 二连号21 22,倒序数22,同尾数09 19 29,就不知道如何用代码表达?
10 03 04 05 09 19 29 3 4 5 9 19 29 ---分析特点 三连号03 04 05,同尾数09 19 29,就不知道如何用代码表达?
11 01 11 21 31 32 33 1 11 21 31 32 33 ---分析特点 三连号31 32 33,同尾数01 11 21 31,就不知道如何用代码表达?
12 18 19 20 21 22 33 18 19 20 21 22 33 ---分析特点 五连号18 19 20 21 22,倒序数33,就不知道如何用代码表达?
ID Txt No1 No2 No3 No4 No5 No6
1 09 14 15 20 26 32 9 14 15 20 26 32
NoText 字节类型为 [varchar](20),取NO2的个位加上 NO3的个位就是 NO1,这个语句不行啊
SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1)=NO1
select *from #History
where 1=1
and Cast(SUBSTRING(NoText,5,1) as int )+ Cast(SUBSTRING(NoText,8,1) as int)=no1
SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1) = 9
ID Txt No1 No2 No3 No4 No5 No6
1 09 14 15 20 26 32 9 14 15 20 26 32
NoText 字节类型为 [varchar](20),取NO2的个位加上 NO3的个位就是 NO1,这个语句不行啊
SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1)=NO1
[/quote]
上面已经回复用cast转换,不然
SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1) = 45 -- 字符串拼接
不是
SUBSTRING(NoText,5,1)+SUBSTRING(NoText,8,1) = 9 -- 等于45!!!if object_id('tempdb..#History') is not null drop table #History
go
CREATE TABLE #History(
[序号][int],[NoText]varchar(20),[NO1]int,[NO2]int,[NO3]int,[NO4]int,[NO5]int,[NO6]int,
MaskStr varchar(33) DEFAULT '000000000000000000000000000000000'
)
insert into #History ([序号],NoText,NO1,NO2,NO3,NO4,NO5,NO6)
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
UPDATE #History
SET MaskStr = STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(MaskStr,NO1,1,'1'),
NO2,1,'1'),
NO3,1,'1'),
NO4,1,'1'),
NO5,1,'1'),
NO6,1,'1')
SELECT [序号],
NoText,
MaskStr AS [123456789012345678901234567890123]
FROM #History
序号 NoText 123456789012345678901234567890123
----------- -------------------- ---------------------------------
1 09 14 15 20 26 32 000000001000011000010000010000010
2 14 17 25 27 28 30 000000000000010010000000101101000
3 13 20 22 26 28 31 000000000000100000010100010100100
4 02 08 09 14 28 30 010000011000010000000000000101000
5 06 07 16 18 29 32 000001100000000101000000000010010
6 06 07 16 18 23 32 000001100000000101000010000000010
7 05 08 15 17 25 33 000010010000001010000000100000001
8 05 08 11 12 18 28 000010010011000001000000000100000
9 09 19 21 22 27 29 000000001000000000101100001010000
10 03 04 05 09 19 29 001110001000000000100000000010000
11 01 11 21 31 32 33 100000000010000000001000000000111
12 18 19 20 21 22 33 000000000000000001111100000000001
USE SSBallDB
GO
; WITH cte(ID,Txt,No1,No2,No3,No4,No5,No6) AS(
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
)
--“倒叙”的算法:
SELECT * FROM cte
WHERE ltrim(No1)+20=ltrim(No2)+ltrim(No3) or ltrim(No4)+ltrim(No6)=2*ltrim(No5)
or No2+No3=No1+NO5 and No5=2*NO1 or Txt like '%7%'
or No3+No4=No2+NO5 and No4=2*NO1
or No5=2*No4
or No1+10=No3 and (No4=3*No1) and (No6=2*No3)
OR ( CHARINDEX('01',Txt,1) > 0 AND CHARINDEX('10',Txt,1) > 0 ) -- 01--10
OR ( CHARINDEX('02',Txt,1) > 0 AND CHARINDEX('20',Txt,1) > 0 ) -- 02--20
OR ( CHARINDEX('03',Txt,1) > 0 AND CHARINDEX('30',Txt,1) > 0 ) -- 03--30
OR ( CHARINDEX('12',Txt,1) > 0 AND CHARINDEX('21',Txt,1) > 0 ) -- 12--21
OR ( CHARINDEX('13',Txt,1) > 0 AND CHARINDEX('31',Txt,1) > 0 ) -- 13--31
OR ( CHARINDEX('23',Txt,1) > 0 AND CHARINDEX('32',Txt,1) > 0 ) -- 23--32
OR ( CHARINDEX('11',Txt,1) > 0 ) -- 11
OR ( CHARINDEX('22',Txt,1) > 0 ) -- 22
OR ( CHARINDEX('33',Txt,1) > 0 ) -- 33
-- 同尾算法:
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','1,','') ) >= 4 --搜索 01,11,21,[31] 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','2,','') ) >= 4 --搜索 02,12,22,[32] 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','3,','') ) >= 4 --搜索 03,13,23,[33] 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','4,','') ) >= 4 --搜索 04,14,24 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','5,','') ) >= 4 --搜索 05,15,25 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','6,','') ) >= 4 --搜索 06,16,26 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','7,','') ) >= 4 --搜索 07,15,27 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','8,','') ) >= 4 --搜索 08,18,28 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','9,','') ) >= 4 --搜索 09,19,29 这种类型
--- 二连号算法
OR ABS(CAST(No2 AS INT) - CAST(No1 AS INT)) = 1 -- 第二列 与 第一列
OR ABS(CAST(No3 AS INT) - CAST(No3 AS INT)) = 1 -- 第三列 与 第二列
OR ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1 -- 第四列 与 第三列
OR ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1 -- 第五列 与 第四列
OR ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1 -- 第六列 与 第五列
-- 三连号算法
OR
(CAST(No2 AS INT) - CAST(No1 AS INT) = 1
AND ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
) -- 第一列,第二列,第三列
OR
(CAST(No3 AS INT) - CAST(No2 AS INT) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
) -- 第二列,第三列,第四列
OR
(CAST(No4 AS INT) - CAST(No3 AS INT) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
) -- 第三列,第四列,第五列
OR
(CAST(No5 AS INT) - CAST(No4 AS INT) = 1
AND ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1
) -- 第四列,第五列,第六列
-- 四连号算法,
OR
(CAST(No2 AS INT) - CAST(No1 AS INT) = 1
AND ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
) -- 第一列,第二列,第三列,第四列
OR
(CAST(No3 AS INT) - CAST(No2 AS INT) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
) -- 第二列,第三列,第四列,第五列
OR
(CAST(No4 AS INT) - CAST(No3 AS INT) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
AND ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1
) -- 第三列,第四列,第五列 ,第六列
-- 五连号算法,
OR
(CAST(No2 AS INT) - CAST(No1 AS INT) = 1
AND ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
) -- 第一列,第二列,第三列,第四列,第五列
OR
(CAST(No3 AS INT) - CAST(No2 AS INT) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
AND ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1
) -- 第二列,第三列,第四列,第五列,第六列
结果差一行数据,找了半天,没有找到原因
ID Txt No1 No2 No3 No4 No5 No6
2 14 17 25 27 28 30 14 17 25 27 28 30
3 13 20 22 26 28 31 13 20 22 26 28 31
4 02 08 09 14 28 30 2 8 9 14 28 30
5 06 07 16 18 29 32 6 7 16 18 29 32
6 06 07 16 18 23 32 6 7 16 18 23 32
7 05 08 15 17 25 33 5 8 15 17 25 33
8 05 08 11 12 18 28 5 8 11 12 18 28
9 09 19 21 22 27 29 9 19 21 22 27 29
10 03 04 05 09 19 29 3 4 5 9 19 29
11 01 11 21 31 32 33 1 11 21 31 32 33
12 18 19 20 21 22 33 18 19 20 21 22 33
差这行记录 ID=1的记录
ID Txt No1 No2 No3 No4 No5 No6
1 09 14 15 20 26 32 9 14 15 20 26 32
ID=1的记录是符合这个条件
SELECT * FROM cte
WHERE ltrim(No1)+20=ltrim(No2)+ltrim(No3) or ltrim(No4)+ltrim(No6)=2*ltrim(No5)
or No2+No3=No1+NO5 and No5=2*NO1 or Txt like '%7%'
or No3+No4=No2+NO5 and No4=2*NO1
or No5=2*No4
or No1+10=No3 and (No4=3*No1) and (No6=2*No3)
求解
AND ( (No2 > No1 AND No3 > No2) -- 升序
OR (No1 > No2 AND No2 > No3) -- 降序
[/quote]
如果默认是升序的话,代码可以简化:
; WITH cte(ID,Txt,No1,No2,No3,No4,No5,No6) AS(
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
)
-- 连号(3连): 前后相差为1,例如:[默认是升序]
-- 02 03 04
SELECT * FROM cte WHERE
(CAST(No2 AS INT) - CAST(No1 AS INT) = 1
AND ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
) -- 第一列,第二列,第三列
OR
(CAST(No3 AS INT) - CAST(No2 AS INT) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
) -- 第二列,第三列,第四列
OR
(CAST(No4 AS INT) - CAST(No3 AS INT) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
) -- 第三列,第四列,第五列
OR
(CAST(No5 AS INT) - CAST(No4 AS INT) = 1
AND ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1
) -- 第四列,第五列,第六列
AND ( (No2 > No1 AND No3 > No2) -- 升序
OR (No1 > No2 AND No2 > No3) -- 降序
; WITH cte(ID,Txt,No1,No2,No3,No4,No5,No6) AS(
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
)
-- 同尾: 至少有三个数尾号相同,例如:
-- 1) 01,11,21 (3个)
-- 2) 02,12,22,32 (4个)
SELECT *
FROM cte
WHERE
LEN(REPLACE(txt,' ',',') + ',') - LEN(REPLACE(REPLACE(txt,' ',',') +',' ,'1,','') ) >= 6 --搜索 01,11,21,[31] 这种类型
OR LEN(REPLACE(txt,' ',',') + ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'2,','') ) >= 6 --搜索 02,12,22,[32] 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'3,','') ) >= 6 --搜索 03,13,23,[33] 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'4,','') ) >= 6 --搜索 04,14,24 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'5,','') ) >= 6 --搜索 05,15,25 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'6,','') ) >= 6 --搜索 06,16,26 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'7,','') ) >= 6 --搜索 07,15,27 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',' ,'8,','') ) >= 6 --搜索 08,18,28 这种类型
OR LEN(REPLACE(txt,' ',',')+ ',') - LEN(REPLACE(REPLACE(txt,' ',',')+',','9,','') ) >= 6 --搜索 09,19,29 这种类型
-- 同尾: 至少有三个数尾号相同,例如:
-- 1) 01,11,21 (3个)
-- 2) 02,12,22,32 (4个)
请教:有2个或2个以上个数尾号相同的就视为同尾数啊,,是不是可以做以下修改
WHERE LEN(Txt) - LEN(REPLACE(Txt,'1 ','') ) >=4
OR LEN(Txt) - LEN(REPLACE(Txt,'2 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'3 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'4 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'5 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'6 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'7 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'8 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'9 ','') ) >= 4
盼解[/quote]
是的[/quote]
对了,保持Txt这个字段的时候,在最后加上一个空格,像这样:
‘14 17 25 27 28 30 ' --30后面留空格
-- 同尾: 至少有三个数尾号相同,例如:
-- 1) 01,11,21 (3个)
-- 2) 02,12,22,32 (4个)
请教:有2个或2个以上个数尾号相同的就视为同尾数啊,,是不是可以做以下修改
WHERE LEN(Txt) - LEN(REPLACE(Txt,'1 ','') ) >=4
OR LEN(Txt) - LEN(REPLACE(Txt,'2 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'3 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'4 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'5 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'6 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'7 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'8 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'9 ','') ) >= 4
盼解[/quote]
是的
-- 同尾: 至少有三个数尾号相同,例如:
-- 1) 01,11,21 (3个)
-- 2) 02,12,22,32 (4个)
请教:有2个或2个以上个数尾号相同的就视为同尾数啊,,是不是可以做以下修改
WHERE LEN(Txt) - LEN(REPLACE(Txt,'1 ','') ) >=4
OR LEN(Txt) - LEN(REPLACE(Txt,'2 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'3 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'4 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'5 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'6 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'7 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'8 ','') ) >= 4
OR LEN(Txt) - LEN(REPLACE(Txt,'9 ','') ) >= 4
盼解; WITH cte(ID,Txt,No1,No2,No3,No4,No5,No6) AS(
SELECT '1','09 14 15 20 26 32','9','14','15','20','26','32' UNION ALL
SELECT '2','14 17 25 27 28 30','14','17','25','27','28','30' UNION ALL
SELECT '3','13 20 22 26 28 31','13','20','22','26','28','31' UNION ALL
SELECT '4','02 08 09 14 28 30','2','8','9','14','28','30' UNION ALL
SELECT '5','06 07 16 18 29 32','6','7','16','18','29','32' UNION ALL
SELECT '6','06 07 16 18 23 32','6','7','16','18','23','32' UNION ALL
SELECT '7','05 08 15 17 25 33','5','8','15','17','25','33' UNION ALL
SELECT '8','05 08 11 12 18 28','5','8','11','12','18','28' UNION ALL
SELECT '9','09 19 21 22 27 29','9','19','21','22','27','29' UNION ALL
SELECT '10','03 04 05 09 19 29','3','4','5','9','19','29' UNION ALL
SELECT '11','01 11 21 31 32 33','1','11','21','31','32','33' UNION ALL
SELECT '12','18 19 20 21 22 33','18','19','20','21','22','33'
)
-- 连号(3连): 前后相差为1,例如:
-- 1) 02 03 04 -- 升序
-- 2) 09 08 07 -- 降序
SELECT * FROM cte WHERE
(ABS(CAST(No2 AS INT) - CAST(No1 AS INT)) = 1
AND ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
AND ( (No2 > No1 AND No3 > No2) -- 升序
OR (No1 > No2 AND No2 > No3) -- 降序
)) -- 第一列,第二列,第三列
OR
(ABS(CAST(No3 AS INT) - CAST(No2 AS INT)) = 1
AND ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
AND ( (No3 > No2 AND No4 > No3) -- 升序
OR (No2 > No3 AND No3 > No4) -- 降序
)) -- 第二列,第三列,第四列
OR
(ABS(CAST(No4 AS INT) - CAST(No3 AS INT)) = 1
AND ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
AND ( (No4 > No3 AND No5 > No4) -- 升序
OR (No3 > No4 AND No4 > No5) -- 降序
)) -- 第三列,第四列,第五列
OR
(ABS(CAST(No5 AS INT) - CAST(No4 AS INT)) = 1
AND ABS(CAST(No6 AS INT) - CAST(No5 AS INT)) = 1
AND ( (No5 > No4 AND No6 > No5) -- 升序
OR (No4 > No5 AND No5 > No6) -- 降序
)) -- 第四列,第五列,第六列