SQL SERVER 公共表达式CTE的应用

【标签】：公共表达式应用、CTE、WITH SQ AS 递归应用、递归获取层级、递归填充空数据、递归填充数据

1、概念

CTE(Common Table Expression) 公用表达式，它是在单个语句的执行范围内定义的临时结果集，只在查询期间有效。它可以自引用，也可在同一查询中多次引用，实现了代码段的重复利用。

CTE最大的好处是提升T-Sql代码的可读性,可以更加优雅简洁的方式实现递归等复杂的查询。

CTE可用于：

　　⒈ 创建递归查询，这个应该是CTE最好用的地方

　　⒉ 在同一语句中多次引用生成的表

　　3. 减少子查询和表变量，提高执行效率

CTE优点：

　　1. 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。同时，CTE要比表变量的效率高得多。

　　2. 可以用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

　　3. 查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

2、语法

CTE 基本语法如下：

WITH cte_name ( column_name [,...n] ) AS 
(        
    CTE_query_definition 
)
SELECT * FROM cte_name

主要包括3个部分：

　　　(1).Expression_name:CTE表达式的名称。

　　　(2).Column_name:列名列表。

　　　(3).CTE_query_definition:定义CTE结果集的Select查询语句

CTE 递归语法如下：

WITH cte_name ( column_name [,...n] ) AS 
(
    CTE_query_definition ----1.定位点成员
    UNION ALL               ----2.递归调用(通过UNION ALL、UNION、EXCEPT、INTERSECT)        
    CTE_query_definition ----3.递归成员
 where xxxx = xxx ---4.终止检查（终止检查是隐式的；当上一个调用中未返回行时，递归将停止）
)
SELECT * FROM cte_name
OPTION(MAXRECURSION 20)　--指定最大递归次数为20

CTE 递归结构必须至少包含一个定位点成员和一个递归成员，递归执行的语义如下：

(1).将 CTE 表达式拆分为定位点成员和递归成员。

(2).运行定位点成员，创建第一个调用或基准结果集 (T0)。

(3).运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。

(4).重复步骤 3，直到达到终止检查，返回空集。

(5).返回结果集，这是对 T0 到 Tn 执行 UNION ALL 的结果。

(6).可通过OPTION(MAXRECURSION ) 设置最大递归次数。

3、递归应用--获取层级

主要说明如下：

1.【获取所有下级】

2.【获取所有上级】

3.【获取所有平级】

 --准备数据
CREATE TABLE #TMP_DEPT(ID INT, PID INT, NAME VARCHAR(200), LVL INT);
INSERT INTO #TMP_DEPT
VALUES (1, 0, '总公司', 0),
       (2, 1, '研发部', 1),
       (3, 1, '销售部', 1),
       (4, 1, '财务部', 1),
       (5, 2, '研发一部', 2),
       (6, 2, '研发二部', 2),
       (7, 3, '销售一部', 2),
       (8, 3, '销售二部', 2),
       (9, 3, '销售三部', 2),
       (10, 5, '研发一部小组A', 3),
       (11, 5, '研发一部小组B', 3);
SELECT * FROM #TMP_DEPT;

1.【获取所有下级】

----求一个部门的所有下级，如[研发部] 的所有下级'
----条件:所有部门的父id都等于[研发部]的ID,取到都是下级的
;WITH T (ID, PID, NAME, LVL, REMARK) AS (
    SELECT T.ID,
          T.PID,
          T.NAME,
          T.LVL,
          CAST(T.NAME AS VARCHAR(200)) AS REMARK
      FROM #TMP_DEPT T ----1.定位点成员
     WHERE T.NAME = '研发部'
    UNION ALL ----2.递归调用(通过UNION ALL、UNION、EXCEPT、INTERSECT)
    SELECT  T1.ID,
           T1.PID,
           T1.NAME,
           T1.LVL,
           CAST(T.REMARK  + '>>'+ T1.NAME AS VARCHAR(200)) AS REMARK
      FROM      #TMP_DEPT T1
     INNER JOIN T ON T1.PID = T.ID ----3.递归成员
)
SELECT * FROM T;

2.【获取所有上级】

----求一个部门的所有上级，如[研发一部] 的所有上级'
----条件:[研发一部]的父ID都对应的部门，是其上级部门
;WITH T (ID, PID, NAME, LVL) AS (
    SELECT T.ID,
         T.PID,
         T.NAME,
         T.LVL
      FROM #TMP_DEPT T
     WHERE T.NAME = '研发一部'
    UNION ALL
    SELECT T1.ID,
          T1.PID,
          T1.NAME,
          T1.LVL
      FROM #TMP_DEPT T1
     INNER JOIN T ON T1.ID = T.PID
)
SELECT *  FROM T ORDER BY LVL;

----进一步扩展，求一个部门的所有上级,并将所有上级串起来，如[研发一部] 的所有上级'

----条件:[研发一部]的父ID都对应的部门，是其上级部门
DECLARE @STR VARCHAR(8000);
;WITH T (ID, PID, NAME, LVL) AS (
    SELECT T.ID,
         T.PID,
         T.NAME,
         T.LVL
     FROM #TMP_DEPT T
     WHERE T.NAME = '研发一部'
    UNION ALL
    SELECT T1.ID,
          T1.PID,
          T1.NAME,
          T1.LVL
     FROM  #TMP_DEPT T1
     INNER JOIN T ON T1.ID = T.PID
)
SELECT @STR = ISNULL(@STR, '') + T.NAME + '->'  FROM T ORDER BY LVL;
SET @STR = SUBSTRING(@STR, 1, LEN(@STR) - 2);
SELECT @STR;

3.【获取所有平级】

----求一个部门的所有平级
;WITH T (ID, PID, NAME, LVL) AS (
       SELECT T.ID, T.PID, T.NAME, T.LVL
       FROM #TMP_DEPT T
       WHERE T.NAME = '研发一部'
)
SELECT      T1.ID,
            T1.PID,
            T1.NAME,
            T1.LVL
  FROM      #TMP_DEPT T1
 INNER JOIN T ON T1.PID = T.PID
 ORDER BY T1.LVL;

DROP TABLE #TMP_DEPT;

4、递归应用--填充空数据

根据上一行填充本行的空白栏位，利用公共表达式实现空数据填充；

先看结果：

实现过程：

--1 创建临时表、添加数据
CREATE TABLE #T1 (ID INT PRIMARY KEY, SCORE INT NULL);
INSERT #T1 VALUES (1001, 70), (1002, 83), (1003, NULL), (1004, NULL), (1005, 95), (1006, NULL);
--查看表数据
SELECT * FROM #T1 ORDER BY ID;

--2 递归查看表数据（填充效果）
WITH T1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ROWNO FROM #T1),
     T2 AS (
    SELECT ID,
           SCORE AS SCORENEW,
           ROWNO
      FROM T1
     WHERE SCORE IS NOT NULL ----1.定位点成员
    UNION ALL                         ----2.递归调用(通过UNION ALL、UNION、EXCEPT、INTERSECT)
    SELECT      T1.ID,
                T2.SCORENEW,
                T1.ROWNO
      FROM      T1 ----3.递归成员
     INNER JOIN T2 ON T2.ROWNO + 1 = T1.ROWNO
     WHERE      T1.SCORE IS NULL ----4.终止检查（终止检查是隐式的；当上一个调用中未返回行时，递归将停止）
)
SELECT T.*,T2.SCORENEW ,T2.ROWNO
  FROM #T1  T
INNER JOIN T2 ON T2.ID = T.ID
ORDER BY T.ID;

--3 递归填充空数据
WITH T1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ROWNO FROM #T1),
     T2 AS (
    SELECT ID,
           SCORE AS SCORENEW,
           ROWNO
      FROM T1
     WHERE SCORE IS NOT NULL ----1.定位点成员
    UNION ALL ----2.递归调用(通过UNION ALL、UNION、EXCEPT、INTERSECT)
    SELECT      T1.ID,
                T2.SCORENEW,
                T1.ROWNO
      FROM      T1 ----3.递归成员
     INNER JOIN T2 ON T2.ROWNO + 1 = T1.ROWNO
     WHERE      T1.SCORE IS NULL ----4.终止检查（终止检查是隐式的；当上一个调用中未返回行时，递归将停止）
)
UPDATE T
  SET T.SCORE = T2.SCORENEW
  FROM #T1  T
INNER JOIN T2 ON T2.ID = T.ID
WHERE  T.SCORE IS  NULL;

--4 查看填充后结果
SELECT * FROM #T1 ORDER BY ID;
--删除临时表
DROP TABLE #T1;

posted @ 2020-10-10 14:41 人生黑色阅读(745) 评论(1) 收藏举报

刷新页面返回顶部

人生黑色

SQL SERVER 公共表达式CTE的应用

公告