实战演练T-SQL公用表表达式(CTE)

1.使用CTE进行基础查询

/****** 查询具有根性质的数据集合 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        AND 0 < LEN([Information])
        AND [Version] = '1.0.0.0'
)
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
    AND [ParentGUID] IS NULL
;

View Code

2.利用CTE进行分页查询

阐述一下: 请不要闪过CTE是用来分页的念头, CTE不具备分页功能, 利用CTE可以将晦涩难懂的分页SQL语句进行流化(个人说词)

先来看一下常见的SQL分页语句

/****** A类型 SQL分页语句 ******/
SELECT TOP 08 [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM tTreeList
WHERE 1 = 1
    AND [AIID] NOT IN (
        SELECT TOP ((2 - 1) * 08) [AIID]
            FROM tTreeList
        WHERE 1 = 1
    )
;

/****** B类型 SQL分页语句 ******/
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM tTreeList
WHERE 1 = 1
    ORDER BY [AIID] ASC
OFFSET ((2 - 1) * 08) ROWS
FETCH NEXT 08 ROWS ONLY
;

View Code

然后来看一下利用CTE的SQL分页语句

/****** 利用CTE分页 一级流化 ******/
WITH CTE_L01 ([CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [AIID] ASC) AS [CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM (
            SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
                FROM tTreeList
            WHERE 1 = 1
                AND 0 < LEN([Information])
                AND [Version] = '1.0.0.0'
        ) AS tCTE_Row_Number
)
SELECT [CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE_L01
WHERE 1 = 1
    AND CTE_L01.[CTE_Row_Number]
        BETWEEN ((2 - 1) * 08 + 1) AND (2 * 08)
;

/****** 利用CTE分页 二级流化 ******/
WITH
CTE_L01 ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        AND 0 < LEN([Information])
        AND [Version] = '1.0.0.0'
)
, -- 这个逗号必不可少
CTE_L02 ([CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [AIID] ASC) AS [CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM CTE_L01
    WHERE 1 = 1
)
SELECT [CTE_Row_Number], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE_L02
WHERE 1 = 1
    AND CTE_L02.[CTE_Row_Number] 
        BETWEEN ((2 - 1) * 08 + 1) AND (2 * 08)
;

View Code

数据查询(中)

1.使用CTE进行标准逆向递归查询
阐述一下: 依据数据的隶属关系, 进行自底向上方向的数据提取.
标准: 不对数据进行篡改

/****** 标准逆向递归查询 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合

    -- 递归查询(请注意)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ParentGUID] = tC.[ThisGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
;

View Code

2.使用CTE进行标准正向递归查询

阐述一下: 依据数据的隶属关系, 进行自顶向下方向的数据提取.
标准: 不对数据进行篡改

/****** 标准正向递归查询 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合

    -- 递归查询(请注意)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
;

View Code

个人观察查询结果, 版本如下

　　SQL Server Management Studio 15.0.18386.0
　　SQL Server 管理对象 (SMO) 16.100.46437.65
　　Microsoft Analysis Services 客户端工具 15.0.19618.0
　　Microsoft 数据访问组件 (MDAC) 10.0.19041.1
　　Microsoft MSXML 3.0 5.0 6.0
　　Microsoft .NET Framework 4.0.30319.42000
　　操作系统 10.0.19043
CTE递归查询策略原则表现为: 广度优先

3.使用CTE进行递归查询的错误防范

下面是一个错误的递归查询示例

/****** 逆向递归查询(错误示例) ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合

    -- 递归查询(请注意, 错误的递归查询)
    SELECT tB.[AIID], tB.[ThisGUID], tB.[ParentGUID], tB.[Information], tB.[Version], tB.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ParentGUID] = tC.[ThisGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
;
/****** 错误信息: 消息 530，级别 16，状态 1，语句被终止。完成执行语句前已用完最大递归 100。 ******/

View Code

为防范上面出现的错误, SQL Server 提供了递归限制选项

OPTION (MAXRECURSION 3)

个人观点: 最好的防范是写正确的SQL语句.

数据查询(下)

以正向递归查询为例, 介绍几个非标准的递归查询
非标准: 按照需求对数据进行篡改

1.包含层级信息的CTE递归查询

/****** 非标准正向递归查询(层级) ******/
WITH CTE ([CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询(请注意, 包含层级信息)
    SELECT 0 AS [CTE_Level], tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合
    
    -- 递归查询(请注意, 包含层级信息)
    SELECT tB.[CTE_Level] + 1, tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
SELECT [CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
;

View Code

2.利用层级信息干涉递归查询

/****** 在属于某数据下的数据集合中查找是否包含特定信息的数据 ******/
WITH CTE ([CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT 0 AS [CTE_Level], tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合
    
    -- 递归查询(请注意)
    SELECT CASE -- 只当包含特定信息时递增层级
                WHEN 0 = CHARINDEX('x', tC.[Information])
                THEN tB.CTE_Level
                ELSE tB.CTE_Level + 1
           END, tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
        -- 浅尝辄止干涉递归查询条件
        AND (tB.CTE_Level + 1) < 2 
)
SELECT TOP 1 [CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
    -- 层级筛选条件
    AND 1 = [CTE_Level]
;

View Code

3.利用递归查询构造数据的隶属关系路径

/****** 构造数据的隶属关系路径 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- 定点查询条件
        AND tA.[ThisGUID] = '{F7D59F0A-5D2F-4F4A-98D3-7836A2EEE1D6}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合
    
    -- 递归查询(请注意)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], 
            CASE -- 隶属关系路径拼接
                WHEN 0 < LEN(tC.[Information])
                THEN tB.[Information] + '/' + tC.[Information]
                ELSE tC.[Information]
            END, tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
;

View Code

数据操作(上)

1. 有条件性全局范围数据批量更新
全局: 不限于隶属关系

/****** 对不确定是否有指定的数据进行指定更新 ******/
WITH
CTE_01 ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT TOP 1 [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        --期望条件
        AND 0 < CHARINDEX('x', [Information])
        AND [Version] = '1.0.0.1'

    UNION ALL -- 联合
     
     --'失望'结果
    SELECT NULL, NULL, NULL, NULL, NULL, NULL
)
, -- 这个逗号必不可少
CTE_02 ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT TOP 1 [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        --从期望数据集合中搜索
        FROM CTE_01
    WHERE 1 = 1
)
UPDATE tTreeList SET [Information] = CTE_02.[Information]
    FROM tTreeList AS tMirror, CTE_02
WHERE 1 = 1
    AND tMirror.[Version] = '1.0.0.0'
;

View Code

2. 无条件性局部范围数据批量更新

局部: 限于隶属关系

/****** 统一某数据下所属数据的某字段值 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
        -- 定点查询条件
    WHERE 1 = 1
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- 联合
    
    -- 递归查询(请注意)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version]
            --统一某字段值(请注意)
            , tB.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
)
UPDATE tTreeList SET [Reserved] = tE.[Reserved]
    FROM tTreeList AS tD 
JOIN CTE AS tE
    ON tD.[ThisGUID] = tE.[ThisGUID]
WHERE 1 = 1
;

View Code

数据操作(下)

1. 删除隶属关系数据

/****** 删除树型结构表中某数据及其下所属数据 ******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- 定点查询
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
        -- 定点查询条件
    WHERE 1 = 1
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.1'

    UNION ALL -- 联合
    
    -- 递归查询(请注意)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- 递归查询条件(请注意)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.1'
    WHERE 1 = 1
)
--可以这样
DELETE tTreeList
    FROM tTreeList AS tD
JOIN CTE AS tE
    ON tD.[ThisGUID] = tE.[ThisGUID] 
WHERE 1 = 1
;
--也可以这样
DELETE FROM tTreeList
    WHERE 1 = 1
        AND [ThisGUID] IN (
            SELECT [ThisGUID]
                FROM CTE
            WHERE 1 = 1
        )
;
--但绝不可以这样(灾难性错误)
DELETE FROM CTE
WHERE 1 = 1
;

View Code

2. 删除并修复隶属关系数据

/****** 删除树型结构表中某数据(被删除数据的子数据'自动'依附于被删除数据的父数据) ******/
WITH
tParentCTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        -- 定位被删数据的父数据
        AND [ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND [Version] = '1.0.0.1'
)
, -- 这个逗号必不可少
tChildCTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS ( 
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        -- 定位被删数据的子数据
        AND [ParentGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND [Version] = '1.0.0.1'
)
--修复隶属关系
UPDATE tTreeList SET [ParentGUID] = tParent.[ThisGUID]
    FROM tParentCTE AS tParent, tTreeList AS tMirror
JOIN tChildCTE AS tChild
    ON tMirror.[ThisGUID] = tChild.[ThisGUID]
DELETE 
    FROM tTreeList
WHERE  1 = 1
    --删除该数据
    AND [ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
;

View Code

表结构操作如下

/****** 若表已存在则删除 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tTreeList]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[tTreeList]
GO
;

/****** 创建表 ******/
CREATE TABLE tTreeList(
    [AIID] [int] IDENTITY(1,1) NOT NULL,
    [ThisGUID] [nvarchar](50) NOT NULL,
    [ParentGUID] [nvarchar](128) NULL,
    [Information] [nvarchar](max) NULL,
    [Version] [nvarchar](max) NULL,
    [Reserved] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED ([AIID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
;

/****** 添加表字段注释 ******/
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'AIID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本GUID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'ThisGUID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父GUID', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'ParentGUID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'Information'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'Version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'Reserved'
GO
;

/****** 批量添加数据 ******/
INSERT INTO tTreeList([ThisGUID], [ParentGUID], [Information], [Version], [Reserved])
SELECT [ThisGUID], [ParentGUID], [Information], [Version], [Reserved] from tTreeList--Bak
WHERE 1 = 1
;

/****** 检查数据 ******/
SELECT * FROM tTreeList
;

View Code

关于为什么使用字符类型的GUID字段作为数据隶属关系的参考, 而不使用自带的自增整型字段, 原因有多方面:
一方面可能会出现N对1的情况, 即多个同级父属, 1个子级
另一方面可能会出现数据在不同库之间迁入迁出的情况
还会有啥情况?

当然这样做也是有不少弊端的, 事物是存在多面性的
其中一个比较严重的问题就是: 当结构中的数据关系出现广度和深度不容小觑时, 查询操作用一个慢字已经不足以形容了
一种缓和的解决方案就是针对相关字段创建索引, 对tTreeList表构建了如下索引

/****** 创建索引 ******/
CREATE UNIQUE INDEX uiThisGUID ON tTreeList([ThisGUID])
CREATE NONCLUSTERED INDEX nciParentGUID ON tTreeList([ParentGUID])

View Code

posted @ 2022-01-25 20:35 wjshan0808 阅读(168) 评论(0) 编辑收藏举报

刷新页面返回顶部