加载中…
个人资料
SQLServerGuru
SQLServerGuru
  • 博客等级:
  • 博客积分:0
  • 博客访问:22,826
  • 关注人气:17
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
正文 字体大小:

CTE 复杂查询的好帮手

(2011-04-14 21:31:34)
标签:

sql

server

cte

it

分类: T-SQL编程

Common Table Expression

 

 

    我们有时需要编写由基本的查询语句派生出来的复杂的查询。一种方法是使用视图;另一种方法是在from子句内使用派生表(内联视图),这样开发人员就能获得一个行集,并立即将该行集加入到select语句中的其他表、视图或用于定义函数中;而在SQL Server 2005中,就可以使用CTECTE能改善代码的可读性和可维护性,而且不会影响性能,此外,相对于SQL Server早期版本,更容易编写递归代码。

 

    为了比较以上三种方法的不同之处,我们一起来完成以下查询。首先,运行以下脚本来创建表CustomersOrders,并写入样例数据。

 

use tempdb

if object_id('Customers','U') is not null begin

   drop table Customers

end

if object_id('Orders','U') is not null begin

   drop table Orders

end

Go

create table Customers(

customer_id int,

name        char(20),

gender      char(1)

)

insert Customers select 1,'Amit Paul','M'

insert Customers select 2,'Dhani Lennevald','M'

insert Customers select 3,'Marie Fredriksson','F'

insert Customers select 4,'Per Gessle','M'

create table Orders(

order_id        int,

customer_id     int,

product_id      char(10)

)

insert Orders select 1,3,'B000XGJH1O'

insert Orders select 2,2,'B000GP8448'

insert Orders select 3,2,'B000FPOJOS'

insert Orders select 4,1,'B000FQ2D5E'

insert Orders select 5,3,'B 000G0HJ3K'

insert Orders select 6,2,'B0011WMIME'

insert Orders select 7,1,'B000IONGWM'

select * from Customers

select * from Orders

 

    这个查询的要求是,返回订单总数不超过2的男性客户的订单数据,其中包括客户编号、姓名、性别、订单编号、产品编号,以及该客户的订单总数。

 

方法1:使用视图

 

create view v_OrderCount(customer_id,order_count)

as

select c.customer_id,count(o.order_id)

   from Customers as c

   left outer join Orders as o on c.customer_id=o.customer_id

   group by c.customer_id

Go

 

select c.customer_id as [c.customer_id],c.name as [c.name]

   ,c.gender as [c.gender],o.order_id as [o.order_id]

   ,o.product_id as [o.product_id],oc.order_count

   from Customers as c

   inner join v_OrderCount as oc on oc.customer_id=c.customer_id

   left outer join Orders as o on c.customer_id=o.customer_id

   where c.gender='M' and order_count<=2

 

视图通常用来分解大型的查询,以便用更易读的方式来查询它们,而且无需在临时表中复制或存储数据。如果权限许可,这个视图还能在整个数据库中被重复使用。

然而,当您想要收集数据并且只使用一次的时候,视图未必是最佳解决方案。因为视图是存在于数据库中的对象,那么创建仅用于单个 T-SQL 查询的视图就有些多余。

 

方法2:使用派生表

 

select c.customer_id as [c.customer_id],c.name as [c.name]

   ,c.gender as [c.gender],o.order_id as [o.order_id]

   ,o.product_id as [o.product_id],oc.order_count

   from Customers as c

   inner join (select c.customer_id,count(o.order_id)

                  from Customers as c

                  left outer join Orders as o

                  on c.customer_id=o.customer_id

                  group by c.customer_id

              ) as oc(customer_id,order_count)

   on oc.customer_id=c.customer_id

   left outer join Orders as o on c.customer_id=o.customer_id

   where c.gender='M' and order_count<=2

 

通过在from子句中嵌套一个由括号包围的select语句,并在括号右边给这个select语句指定一个别名,就可以构造一个派生表。接下来就可以像使用表或视图一样查询或联接它。

相对于视图而言,派生表的生存周期仅限于它所在的查询,不需要作为一个对象保存在数据库中。

派生表通常使查询变的难以阅读和维护,如果想在同一个查询中多次使用派生表,必须重复定义该派生表。

 

方法3:使用CTE

 

;with CTE_OrderCount(customer_id,order_count) as(

select c.customer_id,count(o.order_id)

   from Customers as c

   left outer join Orders as o on c.customer_id=o.customer_id

   group by c.customer_id

)select c.customer_id as [c.customer_id],c.name as [c.name]

    ,c.gender as [c.gender],o.order_id as [o.order_id]

    ,o.product_id as [o.product_id],oc.order_count

    from Customers as c

    inner join CTE_OrderCount as oc on oc.customer_id=c.customer_id

    left outer join Orders as o on c.customer_id=o.customer_id

    where c.gender='M' and order_count<=2

 

CTE具有视图和派生表的优点,而没有它们的缺点。使用CTE的代码清晰易读,不需要在数据库中创建对象,而且在一次定义之后可以在同一查询中重复使用。

CTEwith关键字开始,接着是CTE的名称,之后是可选的字段名称列表(不带数据类型),然后是as关键字,之后的括号里就是定义CTE的查询表达式。只有紧跟着括号的第一个查询语句可以引用该CTE

如果CTE之前还有其他语句,就必须在with关键字之前添加一个分号。

 

1:引用另一个CTECTE

 

    使用之前生成的样例数据,要求返回每个客户的订单统计信息,其中包括客户编号、姓名、订单总数以及个人订单总数与平均订单总数的差,精确到小数点后2位。

 

;with CTE_OrderCount(customer_id,name,order_count) as(

select c.customer_id,min(name),count(o.order_id)

   from Customers as c

   left outer join Orders as o on c.customer_id=o.customer_id

   group by c.customer_id

),CTE_AvgOrderCount(avg_order_count) as(

select convert(decimal(9,2),avg(convert(decimal,order_count)))

   from CTE_OrderCount

)select oc.*,(oc.order_count-aoc.avg_order_count) as diff

    from CTE_OrderCount as oc

    cross join CTE_AvgOrderCount as aoc

 

这个例子中,要计算平均订单总数,需要先计算出每个客户的个人订单总数,因此先构造CTE_OrderCount,然后再基于CTE_OrderCount中算出的order_count来构造CTE_AvgOrderCount

各个CTE之间使用逗号分隔,最后的查询可以访问之前所有的CTE

 

2:递归CTE

 

;with CTE_Recursive(col_1,col_2...) as(

-- Anchor Member Query

union all  --

-- Recursive Member Query that references CTE_Recursive

)

 

在定义递归CTE时,至少要包含一个定位点成员和一个递归成员。所有定位点成员必须置于第一个递归成员之前。定位点成员之间必须使用集合运算符union all,union,intersect,except中的某一个。最后一个定位点成员和第一个递归成员之间,以及组合多个递归成员时,只能使用union all集合运算符。

当递归成员返回零记录时,或显式设置了option(maxrecursion n),就会终止递归。

 

    下面的例子中,使用递归CTE来返回每个员工的编号、姓名、直接上级的姓名和编号,以及该员工在汇报关系中的等级:

 

use AdventureWorks

Go

;with CTE_Recursive(

 EmployeeID

,FirstName

,LastName

,M_FirstName

,M_LastName

,ManagerID

,Level) as(

-- Anchor Member Query

select e.EmployeeID,c.FirstName,c.LastName,convert(nvarchar(50),'')

   ,convert(nvarchar(50),''),e.ManagerID,0

   from HumanResources.Employee as e

   join Person.Contact as c on e.ContactID=c.ContactID

   where e.ManagerID is null

union all

-- Recursive Member Query that references CTE_Recursive

select e.EmployeeID,c.FirstName,c.LastName,r.FirstName,r.LastName

   ,e.ManagerID,r.Level+1

   from HumanResources.Employee as e

   join Person.Contact as c on e.ContactID=c.ContactID

   join CTE_Recursive as r on e.ManagerID=r.EmployeeID

)select * from CTE_Recursive order by level,EmployeeID

 

 

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有