间断与孤岛
来源:互联网 发布:php判断是否为数组 编辑:程序博客网 时间:2024/04/29 06:02
原文:点击打开链接
--缺失范围和现有范围(也称间断和孤岛问题)--1、缺失范围(间断)/*收集人:TravyLee时间:2012-03-25如有引用,请标明“此内容源自MSSQL2008技术内幕之T-SQL”*//*求解间断问题有几种方法,小弟我选择性能较高的三种(使用游标的方法省略有兴趣不全的大哥大姐请回复)---------------------------------------------------------------------间断问题的解决方案1;使用子查询step 1:找到间断之前的值,每个值增加一个间隔step 2:对于没一个间断的起点,找出序列中现有得值,再减去一个间隔本人理解为:找到原数据表中的值加一减一是否存在,若有不妥,望纠正生成测试数据:goif object_id('tbl')is not null drop table tblgocreate table tbl(id int not null)goinsert tblvalues(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_rangeend_range 410 1426 2832 3641 */通过以上的相关子查询我们实现了找到原数据表中的间断范围。而且这种方式的效率较其他方式有绝对的优势间断问题的解决方案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--此子查询生成的结果:/* curnxt 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_rangeend_range 4 10 14 26 28 32 36 41 */ */--2、现有范围(孤岛)/*以上测试数据,试下如下输出:/*开始范围 结束范围2 311 1327 2733 3542 42*/和间断问题一样,孤岛问题也有集中解决方案,这里也只介绍三种省略了用游标的实现方案:孤岛问题解决方案1:使用子查询和排名计算step 1:找出间断之后的点,为他们分配行号(这是孤岛的起点)step 2:找出间断之前的点,为他们分配行号(这是孤岛的终点)step 3:以行号相等作为条件,匹配孤岛的起点和终点--实现代码: with startpoints as ( select id,row_number()over(order by id) as rownum from tbl as a where not exists( select 1 from tbl as b where b.id=a.id-1) /* 此查询语句单独运行的结果: idrownum 21 112 273 334 425 */ ), endpoinds as ( select id,row_number()over(order by id) as rownum from tbl as a where not exists( select 1 from tbl as b where b.id=a.id+1) /* 此查询语句单独运行的结果: idrownum 31 132 273 354 425 */ ) select s.id as start_range,e.id as end_range from startpoints as s inner join endpoinds as e on e.rownum=s.rownum--运行结果: /* start_rangeend_range 23 1113 2727 3335 4242*/孤岛问题解决方案2:使用基于子查询的组标识符--直接给出代码:with d as( select id,(select min(b.id) from tbl b where b.id>=a.id and not exists (select * from tbl c where c.id=b.id+1)) as grp from tbl a)select min(id) as start_range,max(id) as end_rangefrom d group by grp/*start_rangeend_range231113272733354242*/孤岛问题解决方案3:使用基于子查询的组标识符:step 1:按照id顺序计算行号: select id ,row_number()over(order by id) as rownum from tbl/*idrownum21321131241352763373483594210*/step 2:生成id和行号的差: select id,id-row_number()over(order by id) as diff from tbl/*iddiff213111812813827213326342635264232*/这里解释一下这样做的原因; 因为在孤岛范围内,这两个序列都以相同的时间间隔来保持增长,所以 这时他们的差值保持不变。只要遇到一个新的孤岛,他们之间的差值就 会增加。这样做的目的为何,第三步将为你说明。step 3:分别取出第二个查询中生成的相同的diff的值的最大id和最小id with t as( select id,id-row_number()over(order by id) as diff from tbl ) select min(id) as start_range,max(id) as end_range from t group by diff/*start_rangeend_range231113272733354242*/求孤岛问题,低三种方法效率较前两种较高,具有比较强的技巧性希望在实际运用中采纳。*/
阅读全文
1 0
- 间断与孤岛
- 间断和孤岛问题处理方法总结
- 格式孤岛、数据孤岛与通讯孤岛
- --确实范围和现有范围(也称间断和孤岛问题)
- 间断与连续之辩
- 高等数学——连续与间断
- 间断点的判断与分类
- 关于垃圾回收的机制与机理之孤岛垃圾
- 朴素贝叶斯算法【变量连续与间断的讨论】
- 【考研】高数函数的连续性与间断点
- 092 高等数学(上)复习:连续与间断
- 高数 01.08函数的连续性与间断点
- 信息孤岛
- 信息孤岛
- 信息孤岛
- 间断总结
- 不能间断
- 间断伽辽金
- 图像配准简介 CV 两幅图像配准
- JavaScript基本概念
- struts2 接受参数总结
- SSH git clone 出现The authenticity of host 192.168.0.xxx can't be established.的问题
- 图像采集
- 间断与孤岛
- 博弈论
- 安卓 代码混淆与打包
- JAVA之类,构造函数和构造代码块
- python实现阶乘
- 简单的python爬虫——贴吧上取邮箱
- 数据结构-堆
- 对在64位Linux下编译动态链接库参数的探究
- 路由器级联