鍍金池/ 問答/數(shù)據(jù)庫/ 使用sql將各組范圍依一定各數(shù)分組，并依此編號

使用sql將各組范圍依一定各數(shù)分組，并依此編號

想請問各位:
我有一個資料表，有以下這幾個columns:post_no, tkt_no, plic, unit
之後我寫了一段sql 專門是找各post_no及tkt_no最大跟最小，其中post_no及tkt_no是相對應(yīng)的。并依zone分組。
sql如下

select min(post_no) +'~'+ max(post_no) as post_no, min(tkt_no) +char(10) + char(13) +  max(tkt_no) as tkt_no, plic, max(unit)
from TEST
where date = '20170721'
group by zone
order by post_no

結(jié)果如下:

clipboard.png

現(xiàn)在我想要將710001~712987及720001~720645分成每1200一組，不滿1200的自成一組，
結(jié)果如下:

clipboard.png

想請問sql的撰寫方式需要使用什麼樣的方式撰寫，請各位給我一個方向。

回答
編輯回答
朽鹿
---模擬測試數(shù)據(jù)
;WITH test(post_no,tkt_no,[Zone],unit)AS(
   SELECT '710001','ZAA865286','A','N' UNION ALL
   SELECT '710630','ZAA865924','A','N' UNION ALL
   SELECT '710631','ZAB598749','B','N' UNION ALL
   SELECT '711261','ZAB599394','B','N' UNION ALL
   SELECT '711262','ZAC383493','C','N' UNION ALL
   SELECT '712987','ZAC385251','C','N' UNION ALL
   SELECT '720001','ZBA543954','D','C' UNION ALL
   SELECT '720407','ZBA865924','D','C' UNION ALL
   SELECT '720408','ZBB236123','E','C' UNION ALL
   SELECT '720512','ZBB236228','E','C' UNION ALL
   SELECT '720513','ZBC193757','F','C' UNION ALL
   SELECT '720645','ZBC193894','F','C'
)
/*
select min(post_no) +'~'+ max(post_no) as post_no, min(tkt_no) +char(10) + char(13) +  max(tkt_no) as tkt_no, [Zone], max(unit)
from TEST
group by zone
order by post_no
*/
select t.min_postno+1200*(sv.number-1) as StartNo,case when t.min_postno+1200*sv.number-1>=t.max_postno then t.max_postno else t.min_postno+1200*sv.number-1 end as EndNo
      ,row_number()over(order by t.groupid,sv.number) as BoxNo
      ,case when t.min_postno+1200*sv.number-1>=t.max_postno then t.max_postno else t.min_postno+1200*sv.number-1 end-(t.min_postno+1200*(sv.number-1))+1

from (
    select left(post_no,2) as groupid, min(post_no) as min_postno,max(post_no) as max_postno,CEILING((convert(int,max(post_no))-min(post_no)+1)/1200.0) as boxcount
    from test as t
    group by left(post_no,2)
) as t
inner join master.dbo.spt_values as sv on sv.type='P' and sv.number between 1 and t.boxcount
StartNo EndNo BoxNo
710001 711200 1 1200
711201 712400 2 1200
712401 712987 3 587
720001 720645 4 645
2017年3月22日 05:51