连续数字区间问题
2010-09-15 16:42 知行思新 阅读(2118) 评论(2) 编辑 收藏 举报这个问题来自于某论坛的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中测试通过。