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

sql sqlserver ms sql

giantkilling 2017-11-27 15:11:28 ‧ 9472 瀏覽

分享至

想請問各位先進:
我有一個資料表，有以下這幾個columns:post_no, tkt_no, plic, unit
之後我寫了一段sql 專門是找各post_no及tkt_no最大跟最小，其中post_no及tkt_no是相對應的。並依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 TEST02
where date = '20170721'
group by zone
order by post_no

結果如下:

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

目前我所想到的解決辦法如下:

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

但要運行時unit0及divided皆是無效的資料行名稱，想請問各位前輩怎麼解決這個問題。
我運行的sql碼的軟體為sql server 2014 management studio

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

攻城屍

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

放在FROM裡面就可以用GROUP BY

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

回應 1
分享
檢舉

攻城屍 iT邦新手 4 級 ‧ 2017-11-28 10:02:45 檢舉

試寫了一下，不知道是不是你要的

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

登入發表回應