SQL Server CTE的一些實用例子

一、引言

CTE(Common Table Expression) 公用表達式，它是在單個語句的執行範圍內定義的臨時結果集，只在查詢期間有效。它可以自引用，也可在同一查詢中多次引用，實現了代碼段的重覆利用。

CTE最大的好處是提升T-Sql代碼的可讀性，可以以更加優雅簡潔的方式實現遞歸等複雜的查詢。

二、測試數據

CREATE TABLE [dbo].[Product](
    [PRD_ID] [INT] NOT NULL,
    [PRD_NAME] [VARCHAR](100) NULL,
    [UP] [NUMERIC](8, 2) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY NONCLUSTERED 
(
    [PRD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (1,'滑鼠',108)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (2,'鍵盤',108)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (3,'記憶體條',150)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (4,'硬碟',300)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (5,'主機',3000)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (6,'顯示器',750)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (7,'U盤',35)
GO

三、實用例子

3.1、基本用法

WITH CTE1(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT
)
SELECT * FROM CTE1

3.2、多次引用

WITH CET1(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT WHERE PRD_ID<5
)
,CET2(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT WHERE PRD_ID<5
)
SELECT * FROM CET1
UNION ALL
SELECT * FROM CET2

3.3、分頁

WITH CET1(ID,[NAME],[ROWID])
AS
(
    SELECT PRD_ID AS ID,PRD_NAME AS NAME,ROW_NUMBER() OVER (ORDER BY PRD_ID) AS ROWID
    FROM PRODUCT
)
SELECT * FROM CET1 WHERE ROWID BETWEEN 1 AND 5

3.4、遞歸

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT INTO @T VALUES (1,NULL)
INSERT INTO @T VALUES (11,1)
INSERT INTO @T VALUES (12,1)
INSERT INTO @T VALUES (13,1)
INSERT INTO @T VALUES (1101,11)
INSERT INTO @T VALUES (1102,11)
INSERT INTO @T VALUES (1201,12)
INSERT INTO @T VALUES (1301,13)
INSERT INTO @T VALUES (1302,13)

;WITH CTE1 AS
(
    SELECT T.ID,T.PARENTID,1 AS [LEVEL]
    FROM @T AS T
    WHERE T.PARENTID IS NULL
    UNION ALL
    SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1 AS [LEVEL]
    FROM @T AS T INNER JOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT * FROM CTE1 ORDER BY [LEVEL]

3.5、遞歸查詢

查詢某個節點下的所有節點。

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT INTO @T VALUES (1,NULL)
INSERT INTO @T VALUES (11,1)
INSERT INTO @T VALUES (12,1)
INSERT INTO @T VALUES (13,1)
INSERT INTO @T VALUES (1101,11)
INSERT INTO @T VALUES (1102,11)
INSERT INTO @T VALUES (1201,12)
INSERT INTO @T VALUES (1301,13)
INSERT INTO @T VALUES (1302,13)

;WITH CTE1 AS
(
    SELECT T.ID,T.PARENTID,1 AS [LEVEL]
    FROM @T AS T
    WHERE T.PARENTID=11
    UNION ALL
    SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1 AS [LEVEL]
    FROM @T AS T INNER JOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT * FROM CTE1 ORDER BY [LEVEL]

3.6、生成連續數字

WITH GCN
AS
(
    SELECT 0 AS ID
    UNION ALL
    SELECT ID+1 FROM GCN WHERE ID<2047
)
--MAXRECURSION：控制遞歸的最⼤次數
SELECT ID FROM GCN OPTION (MAXRECURSION 2047)

3.7、生成連續日期

WITH GCD
AS
(
    SELECT CAST('2022-05-01' AS DATE) AS [DATE]
    UNION ALL
    SELECT DATEADD(D,1,[DATE]) FROM GCD WHERE [DATE]<'2022-05-31'
)
SELECT [DATE] FROM GCD

3.8、生成連續間隔時間點

WITH GCT
AS
(
    SELECT 1 AS ID,CAST('00:00:00' AS TIME(0)) AS TC
    UNION ALL
    SELECT ID+1 AS ID,CAST(DATEADD(MI,30,TC) AS TIME(0)) AS TC FROM GCT WHERE ID<49
)
SELECT * FROM GCT