如有表:
tb1(NUM INT)
INSERT INTO TB1
SELECT 900
UNION ALL SELECT 923
UNION ALL SELECT 924
UNION ALL SELECT 925
UNION ALL SELECT 927
UNION ALL SELECT 929
UNION ALL SELECT 931
UNION ALL SELECT 934
UNION ALL SELECT 935
UNION ALL SELECT 937
UNION ALL SELECT 938
UNION ALL SELECT 939 输出
开始 结束 个数
923 925 3
934 935 2
937 939 3
tb1(NUM INT)
INSERT INTO TB1
SELECT 900
UNION ALL SELECT 923
UNION ALL SELECT 924
UNION ALL SELECT 925
UNION ALL SELECT 927
UNION ALL SELECT 929
UNION ALL SELECT 931
UNION ALL SELECT 934
UNION ALL SELECT 935
UNION ALL SELECT 937
UNION ALL SELECT 938
UNION ALL SELECT 939 输出
开始 结束 个数
923 925 3
934 935 2
937 939 3
create table tb1(NUM INT)
INSERT INTO TB1
SELECT 900
UNION ALL SELECT 923
UNION ALL SELECT 924
UNION ALL SELECT 925
UNION ALL SELECT 927
UNION ALL SELECT 929
UNION ALL SELECT 931
UNION ALL SELECT 934
UNION ALL SELECT 935
UNION ALL SELECT 937
UNION ALL SELECT 938
UNION ALL SELECT 939
goselect num,rid=identity(int,1,1)
into #tb
from tb1select min(num) minNum,max(num) maxNum,count(*) cnt
from #tb
group by (num-rid)
having count(*) > 1drop table tb1,#tb/**************minNum maxNum cnt
----------- ----------- -----------
923 925 3
934 935 2
937 939 3(3 行受影响)
INSERT INTO TB1
SELECT 900
UNION ALL SELECT 923
UNION ALL SELECT 924
UNION ALL SELECT 925
UNION ALL SELECT 927
UNION ALL SELECT 929
UNION ALL SELECT 931
UNION ALL SELECT 934
UNION ALL SELECT 935
UNION ALL SELECT 937
UNION ALL SELECT 938
UNION ALL SELECT 939 select *,no=IDENTITY(int,1,1) into #tb from tb1select min(NUM) [开始],MAX(NUM) [结束],COUNT(1) 个数 from #tb group by NUM-nodrop table #tb/*
开始 结束 个数
----------- ----------- -----------
900 900 1
923 925 3
927 927 1
929 929 1
931 931 1
934 935 2
937 939 3(7 行受影响)
FROM
(SELECT * FROM tb1 AS a WHERE NOT EXISTS(SELECT 1 FROM Tb1 WHERE Num=a.Num-1)) AS a,
(SELECT * FROM tb1 AS a WHERE NOT EXISTS(SELECT 1 FROM Tb1 WHERE Num=a.Num+1)) AS b
WHERE a.Num<=b.Num
GROUP BY a.Num
HAVING MIN(b.Num)-a.Num>0
/*
Num Num 個數
923 925 3
934 935 2
937 939 3
*/
declare @tb1 table (NUM INT)
INSERT INTO @tb1
SELECT 900
UNION ALL SELECT 923
UNION ALL SELECT 924
UNION ALL SELECT 925
UNION ALL SELECT 927
UNION ALL SELECT 929
UNION ALL SELECT 931
UNION ALL SELECT 934
UNION ALL SELECT 935
UNION ALL SELECT 937
UNION ALL SELECT 938
UNION ALL SELECT 939 ;with maco as
(
select 开始=min(NUM),结束=max(NUM),个数=max(NUM)-min(NUM)+1 from
(
select row_number()over (order by num) as rid,* from @tb1
) a group by NUM-RID having(count(1)>1)
)
select * from maco
/*
开始 结束 个数
----------- ----------- -----------
923 925 3
934 935 2
937 939 3
*/