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