急用！如何查找2个或2个以上的连续号码的记录

Question

连续自然数查找连续号码测试sql

Answer 1

CAST(SUBSTRING(notext,16,2) AS int)

Answer 2

----两个连续------
WHERE CAST(SUBSTRING(notext,4,2) AS int)-CAST(SUBSTRING(notext,1,2) AS int)=1
  AND CAST(SUBSTRING(notext,7,2) AS int)-CAST(SUBSTRING(notext,4,2) AS int)<>1
----三个连续------
WHERE CAST(SUBSTRING(notext,4,2) AS int)-CAST(SUBSTRING(notext,1,2) AS int)=1
  AND CAST(SUBSTRING(notext,7,2) AS int)-CAST(SUBSTRING(notext,4,2) AS int)=1
  AND CAST(SUBSTRING(notext,10,2) AS int)-CAST(SUBSTRING(notext,7,2) AS int)<>1

Answer 3

create table #temp([sid] int,[notext] nvarchar(20))insert #temp
select 111, '01 02 03 04 05 06' union all
select 112, '01 02 03 04 05 18' union all
select 113, '01 02 03 04 18 19' union all
select 114, '01 02 03 07 19 20' union all
select 115, '01 02 03 07 20 21' union all
select 116, '01 02 03 07 21 22' union all
select 117, '01 02 03 07 22 23' union all
select 118, '01 02 03 07 23 24' union all
select 119, '01 02 03 07 24 25' union all
select 120, '01 02 03 07 25 26' union all
select 121, '01 02 03 07 26 27' union all
select 122, '01 02 03 07 27 28' union all
select 123, '01 02 03 07 28 29' union all
select 124, '01 02 03 07 29 30' union all
select 125, '01 02 03 07 30 31' union all
select 126, '01 02 03 07 31 32' union all
select 127, '01 02 03 07 32 33' union all
select 128, '01 02 03 08 09 10' union all
select 129, '01 02 03 08 10 13' union all
select 130, '01 02 04 08 10 12'DECLARE @n INT
SET @n=2--2个连续号码的记录，3个则等于3，4个则等于4，以此类推;WITH a1 AS
(
SELECT a.sid,a.notext,b.notext2,b.notext2-ROW_NUMBER() OVER(PARTITION BY a.sid ORDER BY b.notext2) re
FROM
(SELECT sid,notext,notext2=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(notext)),' ','</v><v>')+'</v></root>') FROM #temp) a
OUTER APPLY
(SELECT notext2 = C.v.value('.','NVARCHAR(MAX)') FROM a.notext2.nodes('/root/v') C(v)) b
)
,a2 AS
(
SELECT sid,MIN(re) re FROM a1 GROUP BY SID
)
,a3 as
(
SELECT a.sid
FROM a1 a
JOIN a2 b ON a.SID=b.SID AND a.re=b.re
GROUP BY a.SID
HAVING COUNT(*)=@n
)
SELECT a.*
FROM #temp a
JOIN a3 b ON a.SID=b.SID

Answer 4

优化了一下数据表，把notext分为6个号码，这样是否可以优化代码

--> 建立数据表#temp --数据表#temp里有'notext'字段记录（一共有 178448行数据）
 
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go 
create table #temp([id] int,[notext] nvarchar(20),[NO1] int ,[NO2] int ,[NO3] int ,[NO4] int ,[NO5] int ,[NO6] int )
insert #temp
select 830085, '07 09 14 15 22 31' ,7,9,14,15,22,31  union all
select 830086, '07 09 14 15 22 32' ,7,9,14,15,22,32  union all
select 830087, '07 09 14 15 22 33' ,7,9,14,15,22,33  union all
select 830089, '07 09 14 15 23 25' ,7,9,14,15,23,25  union all
select 830090, '07 09 14 15 23 26' ,7,9,14,15,23,26  union all
select 830091, '07 09 14 15 23 27' ,7,9,14,15,23,27  union all
select 830092, '07 09 14 15 23 28' ,7,9,14,15,23,28  union all
select 830093, '07 09 14 15 23 29' ,7,9,14,15,23,29  union all
select 830094, '07 09 14 15 23 30' ,7,9,14,15,23,30  union all
select 830095, '07 09 14 15 23 31' ,7,9,14,15,23,31  union all
select 830096, '07 09 14 15 23 32' ,7,9,14,15,23,32  union all
select 830097, '07 09 14 15 23 33' ,7,9,14,15,23,33  union all
select 830099, '07 09 14 15 24 26' ,7,9,14,15,24,26  union all
select 830100, '07 09 14 15 24 27' ,7,9,14,15,24,27  union all
select 830101, '07 09 14 15 24 28' ,7,9,14,15,24,28  union all
select 830102, '07 09 14 15 24 29' ,7,9,14,15,24,29  union all
select 830103, '07 09 14 15 24 30' ,7,9,14,15,24,30  union all
select 830104, '07 09 14 15 24 31' ,7,9,14,15,24,31  union all
select 830105, '07 09 14 15 24 32' ,7,9,14,15,24,32  union all
select 830106, '07 09 14 15 24 33' ,7,9,14,15,24,33  union all
select 830108, '07 09 14 15 25 27' ,7,9,14,15,25,27  union all
select 830109, '07 09 14 15 25 28' ,7,9,14,15,25,28  union all
select 830110, '07 09 14 15 25 29' ,7,9,14,15,25,29  union all
select 830111, '07 09 14 15 25 30' ,7,9,14,15,25,30  union all
select 830112, '07 09 14 15 25 31' ,7,9,14,15,25,31  union all
select 830113, '07 09 14 15 25 32' ,7,9,14,15,25,32  union all
select 830114, '07 09 14 15 25 33' ,7,9,14,15,25,33  union all
select 830116, '07 09 14 15 26 28' ,7,9,14,15,26,28  union all
select 830117, '07 09 14 15 26 29' ,7,9,14,15,26,29  union all
select 830118, '07 09 14 15 26 30' ,7,9,14,15,26,30  union all
select 830119, '07 09 14 15 26 31' ,7,9,14,15,26,31  union all
select 830120, '07 09 14 15 26 32' ,7,9,14,15,26,32  union all
select 830121, '07 09 14 15 26 33' ,7,9,14,15,26,33  union all
select 830123, '07 09 14 15 27 29' ,7,9,14,15,27,29  union all
select 830124, '07 09 14 15 27 30' ,7,9,14,15,27,30  union all
select 830125, '07 09 14 15 27 31' ,7,9,14,15,27,31  union all
select 830126, '07 09 14 15 27 32' ,7,9,14,15,27,32  union all
select 830127, '07 09 14 15 27 33' ,7,9,14,15,27,33
-->查找2/3/4/5/6连号记录，但不知是否有遗漏，而且语句太繁琐，请大神修改，学习中，指定N连号查询，也可以查询全部。
SELECT  [ID] ,[NoText],[No1],[No2],[No3],[No4],[No5],[No6]
  FROM #temp
  where 1=1 
  and (No6-No5=1  and No5-No4<>1 and No4-No3<>1 and No3-No2<>1  and No2-No1<>1) ---2个连续号码  
  or  (No6-No5<>1  and No5-No4=1 and No4-No3<>1 and No3-No2<>1  and No2-No1<>1) ---2个连续号码
  or  (No6-No5<>1  and No5-No4<>1 and No4-No3=1 and No3-No2<>1  and No2-No1<>1) ---2个连续号码
  or  (No6-No5<>1  and No5-No4<>1 and No4-No3<>1 and No3-No2=1  and No2-No1<>1) ---2个连续号码
  or  (No6-No5<>1  and No5-No4<>1 and No4-No3<>1 and No3-No2<>1  and No2-No1=1) ---2个连续号码
  or  (No6-No5=1   and No4-No3=1  and  No2-No1=1)                               ---2个连续号码  
  
  or  (No6-No5=1    and No5-No4=1  and No4-No3<>1 and No3-No2<>1 and No2-No1<>1) ---3个连续号码 
  or  (No6-No5<>1   and No5-No4=1  and No4-No3=1  and No3-No2<>1 and No2-No1<>1) ---3个连续号码 
  or  (No6-No5<>1   and No5-No4<>1 and No4-No3=1  and No3-No2=1  and No2-No1<>1) ---3个连续号码 
  or  (No6-No5<>1   and No5-No4<>1 and No4-No3<>1 and No3-No2=1  and No2-No1=1)  ---3个连续号码 
  or  (No6-No5=1   and No5-No4=1 and  No3-No2=1  and No2-No1=1)                  ---3个连续号码
  
  or (No6-No5=1   and No5-No4=1  and No4-No3=1 and No3-No2<>1 and No2-No1<>1) ---4个连续号码 
  or (No6-No5<>1  and No5-No4=1  and No4-No3=1 and No3-No2=1  and No2-No1<>1) ---4个连续号码
  or (No6-No5<>1  and No5-No4<>1 and No4-No3=1 and No3-No2=1  and No2-No1=1)  ---4个连续号码
  
  or (No6-No5=1  and No5-No4=1 and No4-No3=1 and No3-No2=1 and No2-No1<>1)  ---5个连续号码
  or (No6-No5<>1 and No5-No4=1 and No4-No3=1 and No3-No2=1 and No2-No1=1)   ---5个连续号码
  
  or (No6-No5=1  and No5-No4=1 and No4-No3=1 and No3-No2=1  and No2-No1=1) ---6个连续号码
  
drop table #temp

Answer 5

-----------假设数据总是左小右大排列--------------
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go 
create table #temp([sid] int,[notext] nvarchar(20))
insert #temp
select 111, '01 02 03 04 05 06' union all
select 112, '01 02 03 04 05 18' union all
select 113, '01 02 03 04 18 19' union all
select 114, '01 03 05 07 19 20' union all
select 115, '01 02 05 19 20 21' union all
select 116, '01 02 03 07 21 22' union all
select 117, '01 03 04 05 22 23' union all
select 118, '01 02 03 07 23 24' union all
select 119, '01 02 03 04 24 25' union all
select 120, '01 02 03 07 25 26' union all
select 121, '01 09 11 12 26 27' union all
select 122, '01 02 03 07 27 28' union all
select 123, '01 02 03 07 28 29' union all
select 124, '01 02 03 07 29 30' union all
select 125, '01 02 03 07 30 31' union all
select 126, '01 02 03 07 31 32' union all
select 127, '01 02 03 07 32 33' union all
select 128, '01 02 03 08 09 10' union all
select 129, '01 02 03 08 10 13' union all
select 130, '01 02 07 08 10 13'
--SELECT * FROM #temp
;with split as
(
  select sid, notext, CAST(LEFT(notext, 2) AS int) AS stext, 1 AS n
  from #temp
  union all
  select a.sid, a.notext, CAST(SUBSTRING(a.notext, n+3, 2) AS int) AS stext, n+3
  from #temp a join split b ON a.sid=b.sid
  where n<LEN(a.notext)-1
),
t1 as
(
  select sid, notext, stext,
    ROW_NUMBER() over(PARTITION by sid order by stext) n
  from split a
  where not exists(select 1 from split where sid=a.sid and stext=a.stext-1)
  --order by sid, n
),
t2 as
(
  select sid, notext, stext,
    ROW_NUMBER() over(PARTITION by sid order by stext) n
  from split a
  where not exists(select 1 from split where sid=a.sid and stext=a.stext+1)
  --order by sid, n
),
t3 as
(
  select t1.sid, t1.notext, --t1.stext 小号, t2.stext 大号,
    t2.stext+1-t1.stext as 连号数量
  from t1 join t2 on t1.sid=t2.sid and t1.n=t2.n
),
t4 as
(
  select sid, notext, MAX(连号数量) AS 连号数量
  from t3
  group by sid, notext
)
select sid, notext
from t4
where 连号数量=2
/*---------------结果----------------
130 01 02 07 08 10 13
114 01 03 05 07 19 20
121 01 09 11 12 26 27
-------------------------------------*/

Answer 6

-----------假设数据总是左小右大排列--------------
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go 
create table #temp([sid] int,[notext] nvarchar(20))
insert #temp
select 111, '01 02 03 04 05 06' union all
select 112, '01 02 03 04 05 18' union all
select 113, '01 02 03 04 18 19' union all
select 114, '01 03 05 07 19 20' union all
select 115, '01 02 05 19 20 21' union all
select 116, '01 02 03 07 21 22' union all
select 117, '01 03 04 05 22 23' union all
select 118, '01 02 03 07 23 24' union all
select 119, '01 02 03 04 24 25' union all
select 120, '01 02 03 07 25 26' union all
select 121, '01 09 11 12 26 27' union all
select 122, '01 02 03 07 27 28' union all
select 123, '01 02 03 07 28 29' union all
select 124, '01 02 03 07 29 30' union all
select 125, '01 02 03 07 30 31' union all
select 126, '01 02 03 07 31 32' union all
select 127, '01 02 03 07 32 33' union all
select 128, '01 02 03 08 09 10' union all
select 129, '01 02 03 08 10 13' union all
select 130, '01 02 07 08 10 13'
--SELECT * FROM #temp
;with split as
(
  select sid, notext, CAST(LEFT(notext, 2) AS int) AS stext, 1 AS n
  from #temp
  union all
  select a.sid, a.notext, CAST(SUBSTRING(a.notext, n+3, 2) AS int) AS stext, n+3
  from #temp a join split b ON a.sid=b.sid
  where n<LEN(a.notext)-1
),
t1 as
(
  select sid, notext, stext,
    ROW_NUMBER() over(PARTITION by sid order by stext) n
  from split a
  where not exists(select 1 from split where sid=a.sid and stext=a.stext-1)
  --order by sid, n
),
t2 as
(
  select sid, notext, stext,
    ROW_NUMBER() over(PARTITION by sid order by stext) n
  from split a
  where not exists(select 1 from split where sid=a.sid and stext=a.stext+1)
  --order by sid, n
),
t3 as
(
  select t1.sid, t1.notext, --t1.stext 小号, t2.stext 大号,
    t2.stext+1-t1.stext as 连号数量
  from t1 join t2 on t1.sid=t2.sid and t1.n=t2.n
)
select sid, notext, MAX(连号数量) AS 连号数量
from t3
group by sid, notext/*---------------结果----------------
111 01 02 03 04 05 06 6
112 01 02 03 04 05 18 5
113 01 02 03 04 18 19 4
119 01 02 03 04 24 25 4
116 01 02 03 07 21 22 3
118 01 02 03 07 23 24 3
120 01 02 03 07 25 26 3
122 01 02 03 07 27 28 3
123 01 02 03 07 28 29 3
124 01 02 03 07 29 30 3
125 01 02 03 07 30 31 3
126 01 02 03 07 31 32 3
127 01 02 03 07 32 33 3
128 01 02 03 08 09 10 3
129 01 02 03 08 10 13 3
115 01 02 05 19 20 21 3
130 01 02 07 08 10 13 2
117 01 03 04 05 22 23 3
114 01 03 05 07 19 20 2
121 01 09 11 12 26 27 2
-------------------------------------*/

Answer 7

考虑到楼主的大数据量，楼主试试下面这个是否效率更高？-----------不论数据大小如何排列--------------
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go 
create table #temp([sid] int,[notext] nvarchar(20))
insert #temp
select 111, '01 02 03 04 05 06' union all
select 112, '01 02 03 04 05 18' union all
select 113, '01 02 03 04 18 19' union all
select 114, '01 03 05 07 19 20' union all
select 115, '01 02 05 19 20 21' union all
select 116, '01 02 03 07 21 22' union all
select 117, '01 03 04 05 22 23' union all
select 118, '01 02 03 07 23 24' union all
select 119, '01 02 03 04 24 25' union all
select 120, '01 02 03 07 25 26' union all
select 121, '01 09 11 12 26 27' union all
select 122, '01 02 03 07 27 28' union all
select 123, '01 02 03 07 28 29' union all
select 124, '01 02 03 07 29 30' union all
select 125, '01 02 03 07 30 31' union all
select 126, '01 02 03 07 31 32' union all
select 127, '35 32 33 34 35 33' union all
select 128, '11 12 03 08 09 10' union all
select 129, '31 32 03 04 05 33' union all
select 130, '01 02 07 08 10 13'
--SELECT * FROM #temp
go
if OBJECT_ID('f_lh')>0 DROP Function f_lh
go
CREATE FUNCTION f_lh(@s varchar(20))
RETURNS int
AS
/*给定形如'07 05 06 09 10 11'的6个数字组成的17位字符串，
  返回连续增大号码的数量。如有多段连续，取最大值。
  比如输入'07 05 06 09 10 11'则返回3。
*/
BEGIN
  DECLARE @lh int;
  DECLARE @t TABLE(i tinyint, t int);
  INSERT INTO @t
  SELECT 1, CAST(SUBSTRING(@s, 1, 2) AS INT) UNION
  SELECT 2, CAST(SUBSTRING(@s, 4, 2) AS INT) UNION
  SELECT 3, CAST(SUBSTRING(@s, 7, 2) AS INT) UNION
  SELECT 4, CAST(SUBSTRING(@s, 10, 2) AS INT) UNION
  SELECT 5, CAST(SUBSTRING(@s, 13, 2) AS INT) UNION
  SELECT 6, CAST(SUBSTRING(@s, 16, 2) AS INT);
  with t as
  (
    select i, t, 1 n from @t where i=1
    union all
    SELECT a.i, a.t,
      CASE (a.t-t.t) WHEN 1 THEN t.n+1 ELSE 1 END
    FROM @t a join t ON a.i=t.i+1
  )
  select @lh=MAX(n) from t
  return @lh
END
goselect sid, notext, dbo.f_lh(notext) AS 连号数量
from #temp/*---------------结果----------------
sid    notext            连号数量
------------------------------------
111 01 02 03 04 05 06 6
112 01 02 03 04 05 18 5
113 01 02 03 04 18 19 4
114 01 03 05 07 19 20 2
115 01 02 05 19 20 21 3
116 01 02 03 07 21 22 3
117 01 03 04 05 22 23 3
118 01 02 03 07 23 24 3
119 01 02 03 04 24 25 4
120 01 02 03 07 25 26 3
121 01 09 11 12 26 27 2
122 01 02 03 07 27 28 3
123 01 02 03 07 28 29 3
124 01 02 03 07 29 30 3
125 01 02 03 07 30 31 3
126 01 02 03 07 31 32 3
127 35 32 33 34 35 33 4
128 11 12 03 08 09 10 3
129 31 32 03 04 05 33 3
130 01 02 07 08 10 13 2
-----------------------------------*/