连续数字区间问题

这个问题来自于某论坛的SQL Server板块，感觉也是比较典型的问题。在这里我把问题进行简化，并给出2种方案，作为备忘。

问题描述

有表Table_Num，如下：

通过一个查询返回连续数字区间，结果集如下：

解决方案1

select
        Num1.Num as StartNum,
        min(Num2.Num) as EndNum,
        min(Num2.Num) - Num1.Num + 1 as IntervalNums
from
        Table_Num Num1
        inner join
        Table_Num Num2
        on
                Num1.Num <= Num2.Num
where
        (Num1.Num - 1) not in (select Num from Table_Num)
        and
        (Num2.Num + 1) not in (select Num from Table_Num)
group by
        Num1.Num

其中where条件找到了Table_Num中的边界数值，group by和min(Num2.Num)保证了数值的连续性。

解决方案2

select
        Num1.Num as StartNum,
        Num2.Num as EndNum,
        Num2.Num - Num1.Num + 1 as IntervalNums
from
        Table_Num Num1
        inner join
        Table_Num Num2
        on
                Num1.Num <= Num2.Num
        inner join
        Table_Num Num3
        on
                Num3.Num between Num1.Num and Num2.Num
where
        (Num1.Num - 1) not in (select Num from Table_Num)
        and
        (Num2.Num + 1) not in (select Num from Table_Num)
group by
        Num1.Num,
        Num2.Num
having
        count(Num3.Num) = Num2.Num - Num1.Num + 1

个人认为解决方案2比较容易理解，Num1和Num2定位边界数值，Num3为介于Num1和Num2之间的所有数值，而数值的连续性是由having子句count(Num3.Num) = Num2.Num – Num1.Num + 1来保证的。

除了以上两种方法，应该还有其他方法，大家可以一起讨论。上述两段查询在SQL Server 2005中测试通过。