[SQL ]取得每段連續數字的開始與結束(分享做法)
Hello,
工作剛好遇到一個問題:
table 裡有個欄位: ID(型態 int)
裡面可能有多段連續數字(如下例子)
如何取得它們「起點」與「終點」
例子:
ID
--
1
2
3
5
6
8
9
10
希望得到:
Begin | End
-----------
1 | 3
5 | 6
8 | 10
解法(MSSQL):
;with tb1 as (
select ID,row_number() over (order by ID) as num
from table
)
select distinct min(t2.ID) as Begin,max(t2.ID) as End
from tb1 t1 inner join tb1 t2
on t2.ID - t1.ID = t2.num - t1.num
group by t1.ID
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.221.80.36
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1504684576.A.4C9.html
→
09/06 17:07, , 1F
09/06 17:07, 1F
→
09/06 17:07, , 2F
09/06 17:07, 2F