你的位置:首页 > 数据库

[数据库]sql server 公共表达式(CTE)的简单应用


  现在做项目数据访问基本都会选择一种orm框架,它以面向对象的形式屏蔽底层的数据访问形式,让开发人员更集中在业务处理上,而不是和数据库的交互上,帮助我们提高开发效率;例如一些简单的insert、update,我们不需要写insert into...sql 语句,而是直接new一个实体对象,然后db.Insert(entity),看起来是那么清爽;像EF这样比较完善的orm,支持linq语法对数据库进行访问,写起来就更加爽了,有些人甚至认为开发人员可以不用会写sql语句了...但现实不会让你工作得那么轻松,作为开发人员对数据库这一块的学习还是很有必要的;且不说一些灵活性和效率问题,实际工作中用sql的地方还是非常多的,经常在码代码的时候,突然就传来领导的声音,那个某某某,你赶紧给我出一份报表,那个谁谁谁,你赶紧给我出一份XXX的数据...很急。

  最近在码代码时,领导就来一句:嘿man,你给我统计一下所有xxx产品的信息,要快,那边在催了...。这里抽象一下,如下,大概就是找出所有家具产品的信息,这个分类表包含树形结构,ParentId为0是某种分类的根,它下面可能有许多种子节点/叶子节点。这里需要要找的实际就是一个以家具为根的树。

  

测试sql语句:

DECLARE @Product TABLE(ProductId INT, ParentId INT, ProductName NVARCHAR(64))INSERT INTO @ProductVALUES(1,0,'家具'),(2,0,'服装'),(3,1,'大型家具'),(4,1,'小型家具'),(5,2,'男装'),(6,2,'女装'),(7,3,'床'),(8,3,'衣柜'),(9,3,'沙发'),(10,4,'电脑桌'),(11,4,'椅子'),(12,5,'牛仔裤'),(13,5,'衬衫'),(14,6,'裙子')

  这种需求实际很多,有经验的朋友很快就知道怎么写,而实际写法也很简单。知道这是树形结构,在脑海里出现了:自链接查询、子查询、临时表、游标、用程序写代码递归...公共表达式(CTE),OK!CTE的语法如下:

WITH CTE名称[目标列]
AS
(
<定义CTE的内部查询>
)
<对CTE进行查询的外部查询>

  具体来说,CTE属于表表达式,另一种表表达式是派生表(子查询),有时候使用CTE可以优化我们的代码,使我们的代码更加简单、易读。而且CTE支持递归查询,上面的需求写法为:

;WITH cteAS(SELECT * FROM @Product WHERE ProductId = 1 UNION ALL  SELECT p.* FROM @Product p INNER JOIN cte t ON p.ParentId = t.ProductId)SELECT*FROM cte ORDER BY ProductId

  CTE的递归查询主要包含两个部分,定位点成员和递归成员。如上面的查询,UNION ALL 前面的SELECT 就是定位点成员,它是查询的初始化;UNION ALL下面的属于递归成员,我们可以递归查询时,每次都为CTE返回上一次的结果集。例如,初始化时,cte结果是ProductId 1,第一次递归时,会找到ParentId为1的产品,也就是3,4,并且与上一个结果集UNION ALL得到本次结果集返回,再递归时cte就是1,3,4了;而递归的结束条件就是本次查询的结果为空集,此时递归结束,并返回最终结果集。

  另外需要说的是,CTE是虚拟的,sql server会为它重新生成查询语句,直接访问底层对象;所以在一些性能要求较高的地方,还是要通过执行计划来判断是否需要优化,有时候方便是以性能为代价的。