SQL Server 2005 T-SQL学习笔记：CTE

概念：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

示例四（使用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

注意：OPTION(MAXRECURSION 4)用来设置递归时查找的层数，默认是100，如果超过默认或指定的，则会报错．通常我们用一个层数列来过滤指定的层．