Article From:https://segmentfault.com/q/1010000012147029
Question:

I would like to ask you all:
I have a data sheet with the following columns:post_no, tkt_no, Plic, unit
After that, I wrote a SQL to find the largest and smallest post_no and tkt_no, and post_no and tkt_no correspondingly. And grouped according to zone.
sqlas follows

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

The results are as follows:

Now I want to divide 710001~712987 and 720001~720645 into 1200 groups, with less than 1200 being a group.
The results are as follows:

I would like to ask you how to write SQL’s writing method. Please give me a direction.

Answer 0:
---Analog test dataWITH test (post_no, tkt_no, [Zone], unit) AS (SELECT'710001','ZAA865286','A','N' UNION ALLSELECT'710630','ZAA865924','A','N' UNION ALLSELECT'710631','ZAB598749','B','N' UNION ALLSELECT'711261','ZAB599394','B','N' UNION ALLSELECT'711262','ZAC383493','C','N' UNION ALLSELECT'712987','ZAC385251','C','N' UNION ALLSELECT'720001','ZBA543954','D','C' UNION ALLSELECT'720407','ZBA865924','D','C' UNION ALLSELECT'720408','ZBB236123','E','C' UNION ALLSELECT'720512','ZBB236228','E','C' UNION ALLSELECT'720513','ZBC193757','F','C' UNION ALLSELECT'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 TESTGroup by zoneOrder 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 EndNoRow_number () over(order by t.groupid, sv.number) as BoxNoCase 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)) +1FrOM (Select left (post_no, 2) as groupid, min (post_no) as min_postno, max (post_no) max,ILING ((convert (int, max (post_no)) -min (post_no) +1) /1200.0) as boxcountFrom test as tGroup byLeft (post_no, 2)As tInner join master.dbo.spt_values as SV on sv.type='P'and sv.number between1 and t.boxcount
StartNoEndNoBoxNo
71000171120011200
71120171240021200
7124017129873587
7200017206454645

Leave a Reply

Your email address will not be published. Required fields are marked *