SQL2005中怎样判断一组数字是否为连续数?
想问问在一张表中有1到999999个数字,怎样可以找出这些连续数字中缺了那个数字?比如像1,2,3,4,5,8,9,11,15 这组连续数字中没有6,7,10,12,13,14,怎样查询出没有的数字:6,7,10,12,13,14
作者: yyanzh 发布时间: 2011-06-14
SQL code
select distinct t1.number from master..spt_values t1 left join tb t2 on t1.number=t2.id where t1.number between (select MIN(id) from tb) and (select MAX(id) from tb) and t2.id is null
作者: X_0 发布时间: 2011-06-14
SQL code
declare @a table (id int) insert into @a select 1 union all select 3 union all select 5 union all select 6 union all select 7 union all select 10 union all select 12 union all select 14 union all select 16 select distinct b.number from @a a right join master..spt_values b on a.id=b.number where b.number between (select min(id) from @a) and (select max(id) from @a) and a.id is null /* number ----------- 2 4 8 9 11 13 15 */
作者: maco_wang 发布时间: 2011-06-14
http://topic.csdn.net/u/20090713/11/0f4e30d9-2a93-4e4b-900e-ab2734803e3d.html?8463
作者: fredrickhu 发布时间: 2011-06-14