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

 

-- =============================================                  
-- Author:      余波(杭州)                  
-- Create date: 2011/10/3                  
-- Description: 计算缺少范围和已有范围                 
-- ============================================= 
---------缺少范围(间断)
----测试表和测试数据如下:
if OBJECT_ID('t1') is not null
	drop table t1
GO
create table t1
(
	col1 int not null primary key
)
GO
insert into t1
select 1
union
select 2
union
select 3
union
select 100
union
select 101
union 
select 103
union
select 104
union 
select 105
union 
select 106
GO
----先要计算间断，要得到一下的结果
/*
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)
----得到以下结果
/*
col1
3
101
106
*/
----2、找出间断起点(间断起点为间断点+1)
select a.col1+1 as start_range from t1 a where
not exists(select 1 from t1 b where b.col1=a.col1+1) and
a.col1<(select MAX(col1) from t1)  --需要将最后一个值剔除掉
----得到以下结果
/*
start_range
4
102
*/
----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 where
not exists(select 1 from t1 b where b.col1=a.col1+1) and
a.col1<(select MAX(col1) from t1)
----得到正确结果
/*
start_range	end_range
4	99
102	102
*/
----上面只是其中一种思路,下面还有
----将表按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
----得到以下结果
/*
col1	col1_next
1	2
2	3
3	100
100	101
101	103
103	104
104	105
105	106
106	NULL
*/
----筛选差值大于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
)u
where (col1_next-col1>1) and col1_next is not null
----得到结果
/*
start_range	end_range
4	99
102	102
*/
----当然你还可通过row_number()等函数实现，这边提到的只是一种思路

--------已有范围(孤岛)，要得到以下结果
/*
start_range	end_range
1	3
100	101
103	106
*/
--第一种方法,以每组的最大值最为分组因子
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
--结果如下
/*
col1	factor
1	3
2	3
3	3
100	101
101	101
103	106
104	106
105	106
106	106
*/ 
--通过上面的结果得到正确结果
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_range	end_range
1	3
100	101
103	106
*/

--第二种通过row_number()实现
select col1,ROW_NUMBER() over (order by col1)as factor from t1
--得到以下结果
/*
col1	factor
1	1
2	2
3	3
100	4
101	5
103	6
104	7
105	8
106	9
*/
--如果是连续的组，则col1减去factor是一组连续的值
select col1,(col1-factor) as grp from 
(
select col1,ROW_NUMBER() over (order by col1)as factor from t1
)u
--得到结果
/*
col1	grp
1	0
2	0
3	0
100	96
101	96
103	97
104	97
105	97
106	97
*/
--再得到正确值
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_range	end_range
1	3
100	101
103	106
*/