2023年6月27日发(作者：)

sqlserver中的cte从SQL Server 2005开始，提供了CTE（Common Table Expression，公⽤表表达式）的语法⽀持。CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名的结果集，同时CTE也可以⽤在视图的定义中。在CTE中可以包括对⾃⾝的引⽤，因此这种表达式也被称为递归CTE。CTE的优点公⽤表表达式提供的功能其实和视图差不多，但是它不像视图⼀样把SQL语句保存在我们的数据库⾥⾯。微软官⽅给出的使⽤CTE的优势：1.可以编写⼀个递归查询。2.要使⽤⼀个类似视图的功能，但是⼜不想把这个查询SQL语句的定义保存到数据库中。3.要引⽤⼀个返回数据的SQL语句多次，只需要定义⼀次。使⽤CTE可以把复杂的SQL语句按照逻辑分成简单独⽴的⼏个公⽤表表达式（CTE），这样的最⼤优势就是能够提⾼SQL语句的可读性和可维护性。总结就是，CTE主要可以⽤于树结构的递归和简化SQL语句，增加可读性和可维护性。CTE的使⽤场景由于业务需要，我们经常会写⼀些⽐较复杂的SQL语句，⾥⾯可能会包含很多的JOIN或⼦查询，要维护和理清这种N多个表的JOIN关系是⼀件⾮常头疼的事情，⽽使⽤CTE就可以使维护和理解复杂的SQL语句变得简单⼀些。在开发的时候使⽤⼦查询，⼀般是这种情况：需要从⼀个复杂的⼦查询，甚⾄多级⼦查询嵌套。在这种情况下，在整个SQL语句⾥⾯，⽆论你是直接写SQL语句还是把这段SQL语句包装成⼦查询然后⽤别名来访问，当业务需求越来越变得复杂，你可能随时需要修改这个长且复杂的SQL语句段，⽽维护这种复杂的、可读性差的SQL语句简直是噩梦。有了CTE只有，我们就可以使⽤CTE来定义⼀个SQL语句，并且为这个SQL语句执⾏后返回的结果集定义⼀个别名，接下来就可以通过这个别名来引⽤这些预先执⾏返回的数据集，就像使⽤普通的表⼀样。CTE的语法⼀个公⽤表表达式主要包含三个主要部分：名称（WITH后⾯，列名列之前）。2.列名列（可选）。查询语句主体（AS后⾯括起来的内容）。with expression_name (column_name, …) as ( -- cte_query_definition cte查询语句定义)要注意的是，如果要定义多个表达式，需要⽤逗号分隔。使⽤CTE进⾏多次查询CTE是可以在跟随其后的查询中多次引⽤的。with tmp(id) as ( select id from users where name like '杨%';)select * from tmp;select * from orders where userId in (select id from tmp);使⽤CTE递归查询树形记录（向上查询⽗节点或向下查询⼦节点）CTE有⼀个特性就是它是⽀持递归的，即在CTE的查询语句主体中引⽤⾃⾝。这⼀特性常常被⽤在查询树形记录。with subqry(id, pid, name) as ( select , , om cb where = '001001001'　　 union all　　 select , , om cb, subqry where = )select distinct *from subqrywith subqry(id, pid, name) as ( select , , from cb where = '001'　　 union all　　 select , , from cb, subqry where = )select distinct *from subqry使⽤CTE的注意事项后⾯必须紧跟着使⽤CTE的SQL语句，⽐如SELECT、INSERT和UPDATE等，否则CTE将失效（直接报错）。with tmp(id) as ( select id from users where favor = '⽜奶';)select 1;select * from tmp;像上⾯的语句就会报【定义了公⽤表表达式，但没有使⽤】的错。后⾯也可以跟其他的CTE，但是只能使⽤⼀个WITH，多个CTE中间⽤逗号【,】隔开。withmilk(id) as ( select id from users where favor = '⽜奶';),apple(id) as ( select id from users where favor = '苹果';)select id from milk, apple where = 3.如果CTE表达式名称与某个实体表或者视图重名，则紧跟在该CTE后⾯的SQL语句使⽤的仍然是CTE。要注意的是，后⾯再使⽤该同名就是使⽤实体表或视图了。可以引⽤⾃⾝，也可以引⽤在同⼀个WITH⼦句中预先定义的CTE，但是不允许前向引⽤（定义前使⽤）。5.不能在CTE_QUERY_DEFINITION（CTE查询语句定义）中使⽤以下⼦句：（1）COMPUTE或COMPUTE BY（2）ORDER BY（除⾮指定了TOP ⼦句）（3）INTO（4）带有查询提⽰的OPTION ⼦句（5）FOR XML（6）FOR BROWSE6.如果将CTE⽤于批处理的⼀部分的语句中，那么在它之前的语句必须以分号结尾。declare @id intset @id = 0; -- 这⾥必须以分号结尾;with tmp(name) as ( -- 在with前加上分号避免出错 select name from users where unrequitedLove = '静静';)select * from tmp;当然了，⼀个良好的习惯（技巧）是在写CTE的时候统统在WITH前加上分号【;】，这样就能避免出错，⽐如上⾯的语句。总结如果经常写查询的话，⽐如⼀些统计分析或制作报表，CTE是会经常使⽤到的，因为使⽤起来⼗分⽅便，也⼏乎不会有什么副作⽤，在⼀定程度上能够提⾼开发和维护的效率。另外，其递归属性在树形记录的查询中的应⽤⼗分⼴泛，是⼀个要好好掌握的语法。

"我还是很喜欢你，像樵⼈薄暮，倦鸟归栖。"