已有范围和缺失范围的计算

 

-- =============================================                  -- Author:      余波(杭州)                  -- Create date: 2011/10/3                  -- Description: 计算缺少范围和已有范围                 -- ============================================= ---------缺少范围(间断)----测试表和测试数据如下:if OBJECT_ID('t1') is not nulldrop table t1GOcreate table t1(col1 int not null primary key)GOinsert into t1select 1unionselect 2unionselect 3unionselect 100unionselect 101union select 103unionselect 104union select 105union select 106GO----先要计算间断，要得到一下的结果/*start_range       end_range   4                  99  102                 102*/----分三个步骤走----1、找出间断点select a.col1 from t1 a where not exists(select 1 from t1 b where b.col1=a.col1+1)----得到以下结果/*col13101106*/----2、找出间断起点(间断起点为间断点+1)select a.col1+1 as start_range from t1 a wherenot exists(select 1 from t1 b where b.col1=a.col1+1) anda.col1<(select MAX(col1) from t1)  --需要将最后一个值剔除掉----得到以下结果/*start_range4102*/----3、确定间断终点(用子查询为每个间断起点找到下一个已有值，并减去1)select a.col1+1 as start_range,(select MIN(c.col1) from t1 c where c.col1>a.col1)-1 as end_range from t1 a wherenot exists(select 1 from t1 b where b.col1=a.col1+1) anda.col1<(select MAX(col1) from t1)----得到正确结果/*start_rangeend_range499102102*/----上面只是其中一种思路,下面还有----将表按col1=col1_next进行配对，就是当前值与下一个值进行配对，如果差值大于1，则就是要找的值select a.col1,(select MIN(b.col1) from t1 b where b.col1>a.col1)  as col1_next from t1 a----得到以下结果/*col1col1_next12233100100101101103103104104105105106106NULL*/----筛选差值大于1,从而计算得到间断范围select col1+1 as start_range,col1_next-1 as end_range from (select a.col1,(select MIN(b.col1) from t1 b where b.col1>a.col1)  as col1_next from t1 a)uwhere (col1_next-col1>1) and col1_next is not null----得到结果/*start_rangeend_range499102102*/----当然你还可通过row_number()等函数实现，这边提到的只是一种思路--------已有范围(孤岛)，要得到以下结果/*start_rangeend_range13100101103106*/--第一种方法,以每组的最大值最为分组因子select col1 ,(select min(b.col1) from t1 b where b.col1>=a.col1 and not exists(select 1 from t1 c where b.col1+1=c.col1)) as factor from t1 a--结果如下/*col1factor132333100101101101103106104106105106106106*/ --通过上面的结果得到正确结果select MIN(col1) as start_range,MAX(col1) as end_range from (select col1 ,(select min(b.col1) from t1 b where b.col1>=a.col1 and not exists(select 1 from t1 c where b.col1+1=c.col1)) as factor from t1 a )u group by factor /*start_rangeend_range13100101103106*/--第二种通过row_number()实现select col1,ROW_NUMBER() over (order by col1)as factor from t1--得到以下结果/*col1factor112233100410151036104710581069*/--如果是连续的组，则col1减去factor是一组连续的值select col1,(col1-factor) as grp from (select col1,ROW_NUMBER() over (order by col1)as factor from t1)u--得到结果/*col1grp102030100961019610397104971059710697*/--再得到正确值select MIN(col1) as start_range,MAX(col1) as end_range from(select col1,(col1-factor) as grp from (select col1,ROW_NUMBER() over (order by col1)as factor from t1)u)t group by grp/*start_rangeend_range13100101103106*/