一、前言
现在做项目数据访问基本都会选择一种orm框架,它以面向对象的形式屏蔽底层的数据访问形式,让开发人员更集中在业务处理上,而不是和数据库的交互上,帮助我们提高开发效率;例如一些简单的insert、update,我们不需要写insert into...sql 语句,而是直接new一个实体对象,然后db.Insert(entity),看起来是那么清爽;像EF这样比较完善的orm,支持linq语法对数据库进行访问,写起来就更加爽了,有些人甚至认为开发人员可以不用会写sql语句了...但现实不会让你工作得那么轻松,作为开发人员对数据库这一块的学习还是很有必要的;且不说一些灵活性和效率问题,实际工作中用sql的地方还是非常多的,经常在码代码的时候,突然就传来领导的声音,那个某某某,你赶紧给我出一份报表,那个谁谁谁,你赶紧给我出一份XXX的数据...很急。
二、使用CTE统计树形结构
最近在码代码时,领导就来一句:嘿man,你给我统计一下所有xxx产品的信息,要快,那边在催了...。这里抽象一下,如下,大概就是找出所有家具产品的信息,这个分类表包含树形结构,ParentId为0是某种分类的根,它下面可能有许多种子节点/叶子节点。这里需要要找的实际就是一个以家具为根的树。
测试sql语句:
DECLARE @Product TABLE (ProductId INT, ParentId INT, ProductName NVARCHAR(64)) INSERT INTO @Product VALUES (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 cte AS (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会为它重新生成查询语句,直接访问底层对象;所以在一些性能要求较高的地方,还是要通过执行计划来判断是否需要优化,有时候方便是以性能为代价的。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 鸣潮渊武适配武器选什么 渊武四星五星武器推荐
- 618优选技嘉B760M冰雕X,纯白海景房装机小钢炮
- 《荒野大镖客:救赎》或将加入XGP和PS Premium
- 荒野之息 vs 王国之泪!IGN评选最佳Switch游戏TOP25
- 痛仰乐队.2017-今日青年【摩登天空】【WAV+CUE】
- 【柔顺爵士】Threestyle-2024-NoMatterWhat(FLAC)
- 马人乐团.2011-鞭风再起【喜玛拉雅】【WAV+CUE】
- 杨奇谈《黑神话:悟空》:感谢老师们几年来胸怀明月 休休有容
- 《黑神话:悟空》金箍首次亮相!疑致敬《大话西游》
- 石头姐出席戛纳电影节:白色简约裙装超有型!
- 尘歌壶摹本怎么生成的?摹本使用攻略分享
- 《原神》4.6版本更新前瞻 4.6版本活动介绍
- 智中之宝新计划怎么触发?任务完成攻略
- 吴业坤.2015-Kwan.Gor【星娱乐】【FLAC分轨】
- 陈雪凝.2024-我新长出的犄角(EP)【少城时代】【FLAC分轨】