0

## 使用sql將各組範圍依一定個數分組，並依此編號

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 TEST02
where date = '20170721'
group by zone
order by post_no
``````

``````select t0.unit as unit0, floor(cast((t0.post_no-t1.min)as int)/1200) as divided, min(cast(t0.post_no as int)) as min, max(cast(t0.post_no as int)), (cast(max(t0.post_no)-min(t0.post_no)as int)+1) as leng

from TEST02 as t0
inner join (
select unit, cast(min(post_no) as int) as min from TEST02 group by unit
) as t1
on t0.unit = t1.unit
group by unit0, divided

order by min asc
``````

### 1 個回答

0

iT邦新手 5 級 ‧ 2017-11-28 09:10:37

``````SELECT
t.*
FROM
(
SELECT
t0.unit as unit0,
FLOOR(CAST((t0.post_no-t1.min) AS INT)/1200) AS divided,
MIN(CAST(t0.post_no AS INT)) AS min,
MAX(CAST(t0.post_no AS INT)) AS max,
(CAST(MAX(t0.post_no)-MIN(t0.post_no)AS INT)+1) AS leng
FROM
@temp AS t0
INNER JOIN
(
SELECT
unit,
CAST(MIN(post_no) AS INT) AS min
FROM
@temp
GROUP BY unit
) AS t1
ON t0.unit = t1.unit
) AS t
GROUP BY unit0, divided
``````

``````DECLARE
@temp		TABLE
(
post_no		INT,
unit		VARCHAR(1)
)

DECLARE
@i			INT = 1

WHILE(@i < 2787)
BEGIN
INSERT INTO
@temp
VALUES
(
(710000 + @i),
'A'
),
(
(720000 + @i),
'B'
)

SET @i = (@i + 1)
END

SET @i = 1
WHILE(@i < 111)
BEGIN
INSERT INTO
@temp
VALUES
(
(718000 + @i),
'A'
),
(
(727000 + @i),
'B'
)

SET @i = (@i + 1)
END

SELECT
t1.unit,
MIN(t1.post_no) AS startId,
MAX(t1.post_no) AS endId,
ROW_NUMBER() OVER(ORDER BY t1.unit) AS sn,
(MAX(t1.num) - MIN(t1.num) + 1) AS total
FROM
(
SELECT
(post_no / 10000) AS unit,
ROW_NUMBER() OVER(PARTITION BY unit ORDER BY post_no) AS num,
(post_no - (CAST((post_no / 10000) AS INT) * 10000)) AS post_no
FROM
@temp
) AS t1
GROUP BY
t1.unit, ((t1.num - 1) / 1200)
ORDER BY
t1.unit
``````