如何在 T-SQL 中检查一组数字是否连续? [英] How can I check a group of numbers are consecutive in T-SQL?

问题描述

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

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?

DECLARE @Temp TABLE 
(
    IntegerValue INT,
    Processed BIT
)

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

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).

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

推荐答案

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)

这篇关于如何在 T-SQL 中检查一组数字是否连续?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！