一、前言
现在做项目数据访问基本都会选择一种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会为它重新生成查询语句,直接访问底层对象;所以在一些性能要求较高的地方,还是要通过执行计划来判断是否需要优化,有时候方便是以性能为代价的。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持!
sql,server,公共表达式
更新动态
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]