MySQL 缺失範圍問題

神諭丶發表於2014-08-21
MySql
 本例子運用的是range_problem中的表和資料 

  1. create table range_problem(
  2.     a int(10) unsigned not null,
  3.     primary key (a));


  1. insert into range_problem values(1);
  2. insert into range_problem values(2);
  3. insert into range_problem values(3);
  4. insert into range_problem values(48);
  5. insert into range_problem values(50);
  6. insert into range_problem values(51);
  7. insert into range_problem values(52);
  8. insert into range_problem values(53);
  9. insert into range_problem values(66);
  10. insert into range_problem values(67);

最終要得到的缺失範圍為：





方案一及解決思路：
1、找出start_range該間斷點之前的值，然後把該值+1，即為start_range
2、透過間斷點找出下一個值，對該值-1,即為end_range

1、對於間斷點之前的值：


  1. select a
  2. from range_problem a
  3. where not exists (select *
  4.                 from range_problem b
  5.                 where a.a+1 = b.a)
  6. and
  7. a < (select max(a)
  8.      from range_problem);
得出：


若不在父查詢中加上and條件，則a列的最大值67將不會排除在外，此處67被篩選出來無意義。

2、在上面的基礎上，再透過子查詢處理end_range：


  1. select a+1 as start_range,
  2.         (select min(a)-1
  3.         from range_problem c
  4.         where c.a > a.a) as end_range
  5. from range_problem as a
  6. where not exists (select *
  7.                 from range_problem b
  8.                 where a.a+1 = b.a)
  9. and
  10. a < (select max(a)
  11.      from range_problem);

這將得出我們想要的缺失範圍。


方案二及解決思路：
將range_problem中的資料進行位移匹配，如果是連續的值，那麼其差值應該在1，反之大於1：


  1. select a cur,
  2.         (select min(a)
  3.         from range_problem b
  4.         where b.a>a.a) next
  5. from range_problem a


得出：



根據結果我們可以知道，next-cur=1時，值為連續的，不連續的值為(3,48),(48,50),(53,66)
而我們要求的是(4,47),(49,49),(54,65)，即(cur+1,next-1)

在上面的基礎上，再加以修改：


  1. select cur+1 start_range,next-1 end_range
  2. from(select a cur,
  3.             (select min(a)
  4.             from range_problem b
  5.             where b.a > a.a) next
  6.     from range_problem a) c
  7. where next-cur>1;
這將得出我們想要的缺失範圍。

最後：
此處演示的是缺失範圍是整型型別。在生產環境中遇到的型別可能是整型，也可能是時間型別，兩者並非有多大的區別，稍作修改就可以將該例子轉換為時間型別。





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1255445/，如需轉載，請註明出處，否則將追究法律責任。

相關文章