SQL Server CTE的一些实用例子(Some practical examples of 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
————————

1、 Introduction

CTE (common table expression) is a common expression. It is a temporary result set defined within the execution range of a single statement and is only valid during query. It can be self referenced or referenced multiple times in the same query, realizing the reuse of code segments.

The biggest advantage of CTE is to improve the readability of T-SQL code and realize complex queries such as recursion in a more elegant and concise way.

2、 Test data

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、 Practical examples

3.1 basic usage

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

3.2. Multiple references

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 paging

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 recursion

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 recursive query

Query all nodes under a node.

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. Generate continuous numbers

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. Generate continuous date

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. Generate continuous interval time points

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