公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。
CTE 可用于:
处理以前版本中SQL不好现实,不好理解,复杂的查询问题.比如:分页,递归查询...
创建递归查询。有关详细信息,请参阅使用公用表表达式的递归查询 。
在不需要常规使用视图时替换视图,也就是说,不必将定义存储在元数据中。 启用按从标量嵌套 select 语句派生的列进行分组,或者按不确定性函数或有外部访问的函数进行分组。 在同一语句中多次引用生成的表。
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后,这些简单块可用于生成更复杂的临时 CTE,直到生成最终结果集。
基本用法:
WITH
<
name
of
your CTE
>
(
<
column
names
>
)
AS
(
<
actual query
>
)
SELECT
*
FROM
<
name
of
your CTE
>
示例一(基本用法):
with
MyCTE(ID, Name)
as
(
select
EmployeeID
as
ID, FirstName
+
'
'
+
LastName
as
Name
from
HumanResources.vEmployee )
select
*
from
MyCTE
示例二(分页):
with
MyCTE(ID, Name, RowID)
as
(
select
EmployeeID
as
ID, FirstName
+
'
'
+
LastName
as
Name, Row_Number()
over
(
order
by
EmployeeID)
as
RowID
from
HumanResources.vEmployee )
select
*
from
MyCTE
where
RowID
between
1
and
10
示例三(关联CTE):
with OrderCountCTE(SalesPersonID, OrderCount)
as
(
select
SalesPersonID,
count
(
1)
from Sales.SalesOrderHeader
where
SalesPersonID
is
not
null
group
by SalesPersonID
)
select sp.SalesPersonID, sp.SalesYTD, cte.OrderCount
from
OrderCountCTE cte
inner
join Sales.SalesPerson sp
on
cte.SalesPersonID
=
sp.SalesPersonID
order
by
3
示例四(使用CTE的删除):
CREATE
TABLE
Products ( Product_ID
int
NOT
NULL
, Product_Name
varchar
(
25
), Price
money
NULL
,
CONSTRAINT
PK_Products
PRIMARY
KEY
NONCLUSTERED
(Product_ID) )
GO
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
1
,
'
Widgets
'
,
25
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
2
,
'
Gadgets
'
,
50
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
3
,
'
Thingies
'
,
75
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
4
,
'
Whoozits
'
,
90
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
5
,
'
Whatzits
'
,
5
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
6
,
'
Gizmos
'
,
15
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
7
,
'
Widgets
'
,
24
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
8
,
'
Gizmos
'
,
36
)
INSERT
INTO
Products (Product_ID, Product_Name, Price)
VALUES
(
9
,
'
Gizmos
'
,
36
)
GO
--
==================Delete duplicate products=============================
with
DuplicateProdCTE
as
(
select
Min
(Product_ID)
as
Product_ID, Product_Name
from
Products
group
by
Product_Name
having
count
(
1
)
>
1
)
delete
Products
from
Products p
join
DuplicateProdCTE cte
on
cte.Product_Name
=
p.Product_Name
and
p.Product_ID
>
cte.Product_ID
示例五(递归查询):
CREATE
TABLE
Employee_Tree (Employee_NM
nvarchar
(
50
), Employee_ID
int
PRIMARY
KEY
, ReportsTo
int
)
--
insert some data, build a reporting tree
INSERT
INTO
Employee_Tree
VALUES
(
'
Richard
'
,
1
,
NULL
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Stephen
'
,
2
,
1
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Clemens
'
,
3
,
2
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Malek
'
,
4
,
2
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Goksin
'
,
5
,
4
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Kimberly
'
,
6
,
1
)
INSERT
INTO
Employee_Tree
VALUES
(
'
Ramesh
'
,
7
,
5
)
--
with
MyCTE
as
(
select
Employee_ID, Employee_NM,
-
1
as
ReportsTo,
0
as
SubLevel
from
Employee_Tree
where
ReportsTo
is
null
--
root node
union
all
select
e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel
+
1
from
Employee_Tree e, MyCTE
where
e.ReportsTo
=
MyCTE.Employee_ID )
--
select * from MyCTE
select
MyCTE.Employee_NM
as
emp , MyCTE.SubLevel, e.Employee_NM
as
boss
from
MyCTE
left
join
Employee_Tree e
on
MyCTE.ReportsTo
=
e.Employee_ID
--
OPTION(MAXRECURSION 3) --error
--
OPTION(MAXRECURSION 4) --ok
where
SubLevel
<
4