从 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 的语法
一个公用表表达式主要包含三个主要部分:
1.CET 名称(WITH 后面, 列名列之前).
2. 列名列(可选).
3.CET 查询语句主体(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 cb.id,
- cb.pid,
- cb.namefrom cb
- where cb.id = '001001001'
- union all
- select cb.id,
- cb.pid,
- cb.namefrom cb, subqry
- where cb.id = subqry.pid
- )
- select distinct *
- from subqry
- with subqry(id, pid, name) as (
- select cb.id,
- cb.pid,
- cb.name
- from cb
- where cb.id = '001'
- union all
- select cb.id,
- cb.pid,
- cb.name
- from cb, subqry
- where cb.pid = subqry.id
- )
- select distinct *
- from subqry
使用 CTE 的注意事项
1.CTE 后面必须紧跟着使用 CTE 的 SQL 语句, 比如 SELECT,INSERT 和 UPDATE 等, 否则 CTE 将失效(直接报错).
- with tmp(id) as (
- select id from users where favor = '牛奶';
- )
- select 1;
- select * from tmp;
像上面的语句就会报[定义了公用表表达式, 但没有使用] 的错.
2.CTE 后面也可以跟其他的 CTE, 但是只能使用一个 WITH, 多个 CTE 中间用逗号[,] 隔开.
- with
- milk(id) as (
- select id from users where favor = '牛奶';
- ),
- apple(id) as (
- select id from users where favor = '苹果';
- )
- select id from milk, apple where milk.id = apple.id
3. 如果 CTE 表达式名称与某个实体表或者视图重名, 则紧跟在该 CTE 后面的 SQL 语句使用的仍然是 CTE.
要注意的是, 后面再使用该同名就是使用实体表或视图了.
4.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 BROWSE
6. 如果将 CTE 用于批处理的一部分的语句中, 那么在它之前的语句必须以分号结尾.
declare @id int
set @id = 0; -- 这里必须以分号结尾
;with tmp(name) as ( -- 在 with 前加上分号避免出错
- select name from users where unrequitedLove = '静静';
- )
- select * from tmp;
当然了, 一个良好的习惯 (技巧) 是在写 CTE 的时候统统在 WITH 前加上分号[;] , 这样就能避免出错, 比如上面的语句.
总结
如果经常写查询的话, 比如一些统计分析或制作报表, CTE 是会经常使用到的, 因为使用起来十分方便, 也几乎不会有什么副作用, 在一定程度上能够提高开发和维护的效率. 另外, 其递归属性在树形记录的查询中的应用十分广泛, 是一个要好好掌握的语法.
"我还是很喜欢你, 像樵人薄暮, 倦鸟归栖."
来源: https://www.cnblogs.com/yanggb/p/11165276.html