这个问题来自于某论坛的SQL Server板块,感觉也是比较典型的问题。在这里我把问题进行简化,并给出2种方案,作为备忘。
问题描述
有表Table_Num,如下:
Num |
4 |
5 |
6 |
10 |
11 |
12 |
13 |
20 |
通过一个查询返回连续数字区间,结果集如下:
StartNum | EndNum | IntervalNums |
4 | 6 | 3 |
10 | 13 | 4 |
20 | 20 | 1 |
解决方案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中测试通过。