|
概念：Common Table Expression，简称CTE，中文可以叫做，通用表表达式．
用处：处理以前版本中SQL不好现实，不好理解，复杂的查询问题．比如：分页，递归查询．．．
基本用法：
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
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
注意：
OPTION(MAXRECURSION 4)
用来设置递归时查找的层数，默认是100，如果超过默认或指定的，则会报错．通常我们用一个层数列来过滤指定的层．
|
