SQLServerCTE的一些实用例子

  • Z时代
  • 2024-01-10
  • 分类：综合

database

一、引言

CTE(Common Table Expression) 公用表达式，它是在单个语句的执行范围内定义的临时结果集，只在查询期间有效。它可以自引用，也可在同一查询中多次引用，实现了代码段的重复利用。

CTE最大的好处是提升T-Sql代码的可读性，可以以更加优雅简洁的方式实现递归等复杂的查询。

二、测试数据

CREATETABLE[dbo].[Product](
[PRD_ID][INT]NOTNULL,
[PRD_NAME][VARCHAR](100) NULL,
[UP][NUMERIC](8, 2) NULL,
CONSTRAINT[PK_Product]PRIMARYKEYNONCLUSTERED
(
[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
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (1,"鼠标",108)
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (2,"键盘",108)
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (3,"内存条",150)
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (4,"硬盘",300)
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (5,"主机",3000)
INSERTINTO Product (PRD_ID,PRD_NAME,UP) VALUES (6,"显示器",750)
INSERTINTO 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
UNIONALL
SELECT*FROM CET2

3.3、分页

WITH CET1(ID,[NAME],[ROWID])
AS
(
SELECT PRD_ID AS ID,PRD_NAME AS NAME,ROW_NUMBER() OVER (ORDERBY PRD_ID) AS ROWID
FROM PRODUCT
)
SELECT*FROM CET1 WHERE ROWID BETWEEN1AND5

3.4、递归

DECLARE@TTABLE (ID INT, ParentID INT)
INSERTINTO@TVALUES (1,NULL)
INSERTINTO@TVALUES (11,1)
INSERTINTO@TVALUES (12,1)
INSERTINTO@TVALUES (13,1)
INSERTINTO@TVALUES (1101,11)
INSERTINTO@TVALUES (1102,11)
INSERTINTO@TVALUES (1201,12)
INSERTINTO@TVALUES (1301,13)
INSERTINTO@TVALUES (1302,13)
;WITH CTE1 AS
(
SELECT T.ID,T.PARENTID,1AS[LEVEL]
FROM@TAS T
WHERE T.PARENTID ISNULL
UNIONALL
SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1AS[LEVEL]
FROM@TAS T INNERJOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT*FROM CTE1 ORDERBY[LEVEL]

3.5、递归查询

查询某个节点下的所有节点。

DECLARE@TTABLE (ID INT, ParentID INT)
INSERTINTO@TVALUES (1,NULL)
INSERTINTO@TVALUES (11,1)
INSERTINTO@TVALUES (12,1)
INSERTINTO@TVALUES (13,1)
INSERTINTO@TVALUES (1101,11)
INSERTINTO@TVALUES (1102,11)
INSERTINTO@TVALUES (1201,12)
INSERTINTO@TVALUES (1301,13)
INSERTINTO@TVALUES (1302,13)
;WITH CTE1 AS
(
SELECT T.ID,T.PARENTID,1AS[LEVEL]
FROM@TAS T
WHERE T.PARENTID=11
UNIONALL
SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1AS[LEVEL]
FROM@TAS T INNERJOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT*FROM CTE1 ORDERBY[LEVEL]

3.6、生成连续数字

WITH GCN
AS
(
SELECT0AS ID
UNIONALL
SELECT ID+1FROM GCN WHERE ID<2047
)
--MAXRECURSION：控制递归的最⼤次数
SELECT ID FROM GCN OPTION (MAXRECURSION 2047)

3.7、生成连续日期

WITH GCD
AS
(
SELECTCAST("2022-05-01"AS DATE) AS[DATE]
UNIONALL
SELECTDATEADD(D,1,[DATE]) FROM GCD WHERE[DATE]<"2022-05-31"
)
SELECT[DATE]FROM GCD

3.8、生成连续间隔时间点

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

 

以上是 SQLServerCTE的一些实用例子 的全部内容， 来源链接： utcz.com/z/536510.html

回到顶部