美文网首页
sql server中的cte

sql server中的cte

作者: 赵研宇 | 来源:发表于2020-06-24 17:24 被阅读0次

CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名的结果集,同时CTE也可以用在视图的定义中。
在CTE中可以包括对自身的引用,因此这种表达式也被称为递归CTE。

优势

1.可以编写一个递归查询。
2.要使用一个类似视图的功能,但是又不想把这个查询SQL语句的定义保存到数据库中。
3.要引用一个返回数据的SQL语句多次,只需要定义一次。

语法

一个公用表表达式主要包含三个主要部分:
1.CET名称 (WITH后面,列名列之前)。

  1. 列名列 (可选)。
  2. 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 SUBQRE(ID, PID, NAME)  
        AS(
         SELECT        CB.ID
         ,CB.PID
         ,CB.NAME
         FROM     CB
        WHERE  CB.ID='001001001'
         UNION ALL
         SELECT  CB.ID
          ,CB.PID
          ,CB.NAME
          FROM 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

注意事项

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是会经常使用到的,因为使用起来十分方便,也几乎不会有什么副作用,在一定程度上能够提高开发和维护的效率。另外,其递归属性在树形记录的查询中的应用十分广泛,是一个要好好掌握的语法。

相关文章

网友评论

      本文标题:sql server中的cte

      本文链接:https://www.haomeiwen.com/subject/drwrfktx.html