SQL Server CTE的一些实用例子[通俗易懂]

小小码 • 2023-05-16 13:30 • 代码基础 • 阅读 193

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说SQL Server CTE的一些实用例子[通俗易懂],希望您对编程的造诣更进一步.

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

原文地址：https://www.cnblogs.com/atomy/archive/2022/05/13/16265307.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5273.html