如何在T-SQL中检查一组数字是否连续？

If i was to have a table with an integer column containing n number of rows and wanted to check if they were consecutive, how could I do this?

如果我要有一个包含n行的整数列的表,并想检查它们是否连续,我该怎么办呢?

DECLARE @Temp TABLE 
(
    IntegerValue INT,
    Processed BIT
)

I have a solution that works for 3 rows but this is infelxable, if the requirements change then so would the query (ignoring the fact that my sum wouldnt work in this case).

我有一个解决方案,适用于3行,但这是无穷无尽的,如果需求发生变化那么查询也是如此(忽略了我的总和在这种情况下不起作用的事实)。

@SumOfValues = (@FirstValue * @NumOfValues) + @NumOfValues

3 个解决方案

#1

SELECT CASE
         WHEN COUNT(DISTINCT IntegerValue) /*Or COUNT(*) dependant on how
                                            duplicates should be treated */ 
                =  1 + MAX(IntegerValue) - MIN(IntegerValue) THEN 'Y'
         ELSE 'N'
       END
FROM   @Temp

If you want to know where the gaps are you can use

如果您想知道可以使用的间隙在哪里

;WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY IntegerValue) - IntegerValue AS Grp
FROM @Temp
)
SELECT MIN(IntegerValue) AS RangeStart, 
       MAX(IntegerValue) AS RangeEnd
FROM T
GROUP BY Grp
ORDER BY MIN(IntegerValue)

#2

Solution 1: (I have assumed the next value is current value plus 1; also I have added a PK):

解决方案1 :(我假设下一个值是当前值加1;我还添加了一个PK):

DECLARE @Temp TABLE 
(
    IntegerValue INT PRIMARY KEY,
    Processed BIT
);

INSERT  @Temp(IntegerValue)
SELECT  1
UNION ALL
SELECT  2
UNION ALL
SELECT  3
UNION ALL
SELECT  4
UNION ALL
SELECT  5;

--Test 1
DECLARE @RowCount INT;
SELECT  @RowCount=COUNT(*)
FROM    @Temp a
LEFT JOIN @Temp b ON a.IntegerValue+1=b.IntegerValue
WHERE   b.IntegerValue IS NOT NULL
OR      b.IntegerValue IS NULL
AND     a.IntegerValue = (SELECT MAX(IntegerValue) FROM @Temp);

IF @RowCount = (SELECT COUNT(*) FROM @Temp)
    SELECT 'CONSECUTIVE' Test1_Result
ELSE
    SELECT 'not CONSECUTIVE' Test1_Result;

--Test 2
DELETE @Temp;
INSERT  @Temp(IntegerValue)
SELECT  1
UNION ALL
SELECT  2
UNION ALL
SELECT  3
UNION ALL
SELECT  400
UNION ALL
SELECT  5;

SELECT  @RowCount=COUNT(*)
FROM    @Temp a
LEFT JOIN @Temp b ON a.IntegerValue+1=b.IntegerValue
WHERE   b.IntegerValue IS NOT NULL
OR      b.IntegerValue IS NULL
AND     a.IntegerValue = (SELECT MAX(IntegerValue) FROM @Temp);

IF @RowCount = (SELECT COUNT(*) FROM @Temp)
    SELECT 'CONSECUTIVE' Test2_Result
ELSE
    SELECT 'not CONSECUTIVE' Test2_Result;

Solution 2: (less logical reads; the minimum IntegerValue must be 1)

解决方案2 :(逻辑读取较少;最小IntegerValue必须为1)

DECLARE @Temp TABLE 
(
    IntegerValue INT PRIMARY KEY,
    Processed BIT
);

INSERT  @Temp(IntegerValue)
SELECT  v.number
FROM    master.dbo.spt_values v
WHERE   v.type = 'P'
AND     v.number > 0;

SELECT CASE WHEN 
    (
        SELECT  TOP 1 q.IntegerValue
        FROM
        (
            SELECT  a.IntegerValue
                    ,ROW_NUMBER() OVER(ORDER BY a.IntegerValue) Num
            FROM    @Temp a
        ) q
        WHERE   q.IntegerValue <> q.Num
    ) IS NULL THEN 'Y' ELSE 'N' END

Solution 3: ("quirky select" method; less logical reads, lower elapsed time)

解决方案3 :(“古怪选择”方法;逻辑读取较少,经过的时间较短)

DECLARE @Temp TABLE 
(
    IntegerValue INT PRIMARY KEY,
    Processed BIT
);

INSERT  @Temp(IntegerValue)
SELECT  1
UNION ALL
SELECT  2
UNION ALL
SELECT  3
UNION ALL
SELECT  4
UNION ALL
SELECT  5;

DECLARE @IsConsecutive BIT,
    @PreviousIntegerValue INT;

SELECT  @IsConsecutive = 1;

SELECT   @IsConsecutive = CASE WHEN @PreviousIntegerValue + 1 <> a.IntegerValue /*AND a.IntegerValue IS NOT NULL = condition necessary if IntegerValue field allows NULLs */ THEN 0 ELSE @IsConsecutive END
        ,@PreviousIntegerValue = a.IntegerValue
FROM    @Temp a
ORDER BY a.IntegerValue ASC
OPTION (MAXDOP 1);

SELECT  @IsConsecutive [IsConsecutive];

#3

-1

CREATE TABLE #T(ID INT)
DECLARE @i INT
SET @I = 1
WHILE(@I <= LEN('12235588966'))
BEGIN
  IF(SUBSTRING('12235588966',@i,1) = SUBSTRING('12235588966',@i-1,1))
  BEGIN
        INSERT INTO #T SELECT SUBSTRING('12235588966',@i,1)
  END
  SET @i = @I +1
END

SELECT * FROM #T

#1

SELECT CASE
         WHEN COUNT(DISTINCT IntegerValue) /*Or COUNT(*) dependant on how
                                            duplicates should be treated */ 
                =  1 + MAX(IntegerValue) - MIN(IntegerValue) THEN 'Y'
         ELSE 'N'
       END
FROM   @Temp