34,587
社区成员
发帖
与我相关
我的任务
分享
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
*/
SELECT a.Num,MIN(b.Num) AS Num,個數=MIN(b.Num)-a.Num+1
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
*/
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
select *,no=IDENTITY(int,1,1) into #tb from tb1
select min(NUM) [开始],MAX(NUM) [结束],COUNT(1) 个数 from #tb group by NUM-no
drop table #tb
/*
开始 结束 个数
----------- ----------- -----------
900 900 1
923 925 3
927 927 1
929 929 1
931 931 1
934 935 2
937 939 3
(7 行受影响)
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
go
select num,rid=identity(int,1,1)
into #tb
from tb1
select min(num) minNum,max(num) maxNum,count(*) cnt
from #tb
group by (num-rid)
having count(*) > 1
drop table tb1,#tb
/**************
minNum maxNum cnt
----------- ----------- -----------
923 925 3
934 935 2
937 939 3
(3 行受影响)