日期:2014-05-18 浏览次数:20686 次
select 姓名,消费次数 into #t from student;
------解决方案--------------------
--确实范围和现有范围(也称间断和孤岛问题) --1、缺失范围(间断) /* 收集人:TravyLee 时间:2012-03-25 如有引用,请标明“此内容源自MSSQL2008技术内幕之T-SQL” */ /* 求解间断问题有几种方法,小弟我选择性能较高的三种(使用游标的方法省略 有兴趣不全的大哥大姐请回复) --------------------------------- 间断问题的解决方案1;使用子查询 step 1:找到间断之前的值,每个值增加一个间隔 step 2:对于没一个间断的起点,找出序列中现有得值,再减去一个间隔 本人理解为:找到原数据表中的值加一减一是否存在,若有不妥,望纠正 生成测试数据: go if object_id('tbl')is not null drop table tbl go create table tbl( id int not null ) go insert tbl values(2),(3),(11),(12),(13),(27),(33),(34),(35),(42) 要求:找到上表数据中的不存在的id的范围, --实现输出结果: /* 开始范围 结束范围 4 10 14 26 28 32 36 41 */ 按照每个步骤实现: step 1:找到间断之前的值,每个值增加一个间隔 我们可以清楚的发现,要找的间断范围的起始值实际上就是我们 现有数据中的某些值加1后存不存在现有数据表中的问题,通过 子查询实现: select id+1 as start_range from tbl as a where not exists(select 1 from tbl as b where b.id=a.id+1)and id<(select max(id) from tbl) --此查询语句实现以下输出: /* start_range 4 14 28 36 */ step 2:对于没一个间断的起点,找出序列中现有得值,再减去一个间隔 select id+1 as start_range,(select min(b.id) from tbl as b where b.id>a.id)-1 as end_range from tbl a where not exists(select 1 from tbl as b where b.id=a.id+1) and id<(select max(id) from tbl) --输出结果: /* start_range end_range 4 10 14 26 28 32 36 41 */ 通过以上的相关子查询我们实现了找到原数据表中的间断范围。 而且这种方式的效率较其他方式有绝对的优势 间断问题的解决方案2;使用子查询(主意观察同1的区别) step 1:对每个现有的值匹配下一个现有的值,生成一对一对的当 前值和下一个值 step 2:只保留下一个值减当前值大于1的间隔值对 step 3:对剩下的值对,将每个当前值增加1个间隔,将每个下一 个值减去一个间隔 --转换成T-SQL语句实现: --step 1: select id as cur,(select min(b.id) from tbl b where b.id>a.id) as nxt from tbl a --此子查询生成的结果: /* cur nxt 2 3 3 11 11 12 12 13 13 27 27 33 33 34 34 35 35 42 42 NULL */ step 2 and step 3: select cur+1 as start_range,nxt-1 as end_range from (select id as cur,(select min(b.id) from tbl b where b.id>a.id) as nxt from tbl a ) as d where nxt-cur>1 --生成结果: /* start_range end_range 4 10 14 26 28 32 36 41 */ 间断问题的解决方案3;使用排名函数实现 此种方法与第二种类似,这里我一步实现: ;with c as ( select id,row_number()over(order by id) as rownum from tbl ) select cur.id+1 as strat_range,nxt.id-1 as end_range from c as cur join c as nxt on nxt.rownum=cur.rownum+1 where nxt.id-cur.id>1 --输出结果: /* strat_range end_range 4 10 14 26 28 32 36 41 */ */ --2、先有范围(孤岛) /* 以上测试数据,试下如下输出: /* 开始范围 结束范围 2 3 11 13 27 27 33 35 42 42 */ 和间断问题一样,孤岛问题也有集中解决方案,这里也只介绍三种 省略了用游标的实现方案: 孤岛问题解决方案1:使用子查询和排名计算 step 1:找出间断之后的点,为他们分配行号(这是孤岛的起点) step 2:找出间