[SQL]透過common table expressions (CTE)來產生某區間的連續序號

2013-09-30

3970
0
SQL
2014-08-25

SQL CTE (common table expressions) 除了讓我們很方便的以遞迴的方式來取出樹狀結構的資料，還可以產生區間的資料哦!
如連續數值、日期等，本篇介紹如何產生某區間的連續序號

今天發現系統中取序號的Store Procedure的Read很高，詳細看一下才發現，它為了Support取多筆序號，而使用一個數值Table去做Cross Join。

而在SQL 2005之後，有提供 CTE 除了可組出組織的資料外，也可以利用它來產生某個區間的序號。

以下說明原本使用數值Table的做法及使用CTE 的方式，如下，

方法1:利用Table DIGITAL(存放0~9) + CROSS JOIN 出各數值資料

1.建立DIGITAL Table

CREATE TABLE [DIGITAL]
(
[DIGI] [tinyint] NOT NULL
)  
GO
insert into DIGITAL(DIGI) values(0);
insert into DIGITAL(DIGI) values(1);
insert into DIGITAL(DIGI) values(2);
insert into DIGITAL(DIGI) values(3);
insert into DIGITAL(DIGI) values(4);
insert into DIGITAL(DIGI) values(5);
insert into DIGITAL(DIGI) values(6);
insert into DIGITAL(DIGI) values(7);
insert into DIGITAL(DIGI) values(8);
insert into DIGITAL(DIGI) values(9);

2.多個 DIGITAL Table CROSS JOIN 出各數值資料

--目前的序號
DECLARE @CURRENT_SEQ INT
SET @CURRENT_SEQ = 1000;

--要取號的筆數
DECLARE @NUM_COUNT INT 
SET @NUM_COUNT = 12;

--最後的序號
DECLARE @AFTER_SEQ INT
SET @AFTER_SEQ = @CURRENT_SEQ + @NUM_COUNT;

--方法1:利用Table DIGITAL(存放0~9) + CROSS JOIN 出數值資料
SELECT @AFTER_SEQ - ((((A.DIGI*10+B.DIGI)*10+C.DIGI)*10+D.DIGI)*10+E.DIGI) AS SEQ_NO
FROM DIGITAL A 
CROSS JOIN DIGITAL B 
CROSS JOIN DIGITAL C
CROSS JOIN DIGITAL D
CROSS JOIN DIGITAL E 
WHERE (((A.DIGI*10+B.DIGI)*10+C.DIGI)*10+D.DIGI)*10+E.DIGI < @NUM_COUNT
ORDER BY (((A.DIGI*10+B.DIGI)*10+C.DIGI)*10+D.DIGI)*10+E.DIGI DESC;

所以，會產生1001~1012 12筆資料，如下，

方法2:使用 CTE 來產生各序號的資料，如下，

--目前的序號
DECLARE @CURRENT_SEQ INT
SET @CURRENT_SEQ = 1000;

--要取號的筆數
DECLARE @NUM_COUNT INT 
SET @NUM_COUNT = 12;

--最後的序號
DECLARE @AFTER_SEQ INT
SET @AFTER_SEQ = @CURRENT_SEQ + @NUM_COUNT;

;WITH SEQ_RESULT 
AS(
	SELECT @AFTER_SEQ AS SEQ_NO, @NUM_COUNT AS NUM_CNT
		UNION ALL
		SELECT Y.SEQ_NO - 1, Y.NUM_CNT -1
		FROM SEQ_RESULT Y
		WHERE Y.NUM_CNT > 1
)
SELECT SEQ_NO 
FROM SEQ_RESULT 
ORDER BY SEQ_NO;

同樣的，也會產生1001~1012 12筆資料，如下，