求数据结构算法解决办法

孤岛问题解决方案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)      /*     此查询语句单独运行的结果：     id    rownum     2    1     11    2     27    3     33    4     42    5     */    ),    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)   /*     此查询语句单独运行的结果：     id    rownum     3    1     13    2     27    3     35    4     42    5    */    )    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_range    end_range 2    3 11    13 27    27 33    35 42    42*/孤岛问题解决方案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_range    end_range2    311    1327    2733    3542    42*/孤岛问题解决方案3：使用基于子查询的组标识符:step 1:按照id顺序计算行号:   select id ,row_number()over(order by id) as rownum from tbl/*id    rownum2    13    211    312    413    527    633    734    835    942    10*/step 2：生成id和行号的差:   select id,id-row_number()over(order by id) as diff from tbl/*id    diff2    13    111    812    813    827    2133    2634    2635    2642    32*/这里解释一下这样做的原因；   因为在孤岛范围内，这两个序列都以相同的时间间隔来保持增长，所以   这时他们的差值保持不变。只要遇到一个新的孤岛，他们之间的差值就   会增加。这样做的目的为何，第三步将为你说明。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_range    end_range2    311    1327    2733    3542    42*/求孤岛问题，低三种方法效率较前两种较高，具有比较强的技巧性希望在实际运用中采纳。*/孤岛问题，参考我的博客内容
------解决方案--------------------
SQL code
;with ach as(    select 1 as id,100.0000 as num union all    select 2 ,100.0000 union all    select 3 ,100.0000 union all    select 4 ,100.0000 union all    select 7 ,100.0000 union all    select 8 ,100.0000 union all    select 19 ,100.0000 union all    select 22 ,100.0000 union all    select 25 ,100.0000 union all    select 10 ,200.0000 union all    select 11 ,200.0000 union all    select 13 ,200.0000 union all    select 14 ,300.0000 union all    select 15 ,300.0000 union all    select 17 ,300.0000),art as(    select row_number() over (order by getdate()) as rnt,        id,num    from ach),cte as(    select *,rno=row_number() over (partition by num order by rnt)    from art)select min(id) minid,max(id) maxid,numfrom ctegroup by num,id-rno/**************************minid       maxid       num----------- ----------- ---------------------------------------1           4           100.00007           8           100.000010          11          200.000013          13          200.000019          19          100.000014          15          300.000022          22          100.000017          17          300.000025          25          100.0000(9 行受影响)