Practice T-SQL common table expression (CTE)

 

Data query (Part 1)

1. Use CTE for basic query

/****** Query data sets with root properties******/
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
;

2. Paging query using CTE

Explain: please don't miss the idea that CTE is used for paging. CTE doesn't have paging function. Using CTE, you can stream obscure paging SQL statements (personal words)

Let's take a look at the common SQL paging statements

/****** A Type SQL paging statement******/
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 Type SQL paging statement******/
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
;

Then take a look at SQL paging statements using CTE

/****** First level streaming using CTE paging******/
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)
;

/****** Secondary fluidization using CTE paging******/
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'
)
, -- This comma is essential
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)
;

 

Data query (medium)

1. Use CTE for standard reverse recursive query
Explain: according to the subordinate relationship of data, carry out bottom-up data extraction
Standard: no data tampering

/****** Standard reverse recursive query******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union

    -- recursive query (Please note that)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- Recursive query condition(Please note that)
        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
;

2. Standard forward recursive query using CTE

Explain: according to the subordinate relationship of data, carry out top-down data extraction
Standard: no data tampering

/****** Standard forward recursive query******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union

    -- recursive query (Please note that)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- Recursive query condition(Please note that)
        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
;

Personal observation query results, version as follows

　　SQL Server Management Studio 15.0.18386.0
SQL Server management objects (SMO) 16.100.46437.65
Microsoft Analysis Services client tools 15.0.19618.0
Microsoft data access component (MDAC) 10.0.19041.1
　　Microsoft MSXML 3.0 5.0 6.0
　　Microsoft .NET Framework 4.0.30319.42000
Operating system 10.0.19043
The principle of CTE recursive query strategy is: breadth first

3. Error prevention of recursive query using CTE

The following is an example of an incorrect recursive query

/****** Reverse recursive query (error example)******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union

    -- recursive query (Please note that, Bad recursive query)
    SELECT tB.[AIID], tB.[ThisGUID], tB.[ParentGUID], tB.[Information], tB.[Version], tB.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- Recursive query condition(Please note that)
        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
;
/****** Error message: Message 530, level 16, state 1, statement terminated. The maximum recursion of 100 has been used up before completing the execution statement******/

To prevent the above errors, SQL Server provides the recursion restriction option

OPTION (MAXRECURSION 3)

Personal opinion: the best precaution is to write correct SQL statements

 

Data query (Part 2)

Taking forward recursive query as an example, this paper introduces several non-standard recursive queries
Non standard: tamper with data as required

1. CTE recursive query containing hierarchical information

/****** Nonstandard forward recursive query (level)******/
WITH CTE ([CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query(Please note that, Contains hierarchy information)
    SELECT 0 AS [CTE_Level], tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union
    
    -- recursive query (Please note that, Contains hierarchy information)
    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
        -- Recursive query condition(Please note that)
        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
;

2. Use hierarchical information to interfere with recursive query

/****** Find the data containing specific information in the data set under a certain data******/
WITH CTE ([CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT 0 AS [CTE_Level], tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union
    
    -- recursive query (Please note that)
    SELECT CASE -- Increment the hierarchy only if it contains specific information
                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
        -- Recursive query condition(Please note that)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.0'
    WHERE 1 = 1
        -- On interference recursive query conditions
        AND (tB.CTE_Level + 1) < 2 
)
SELECT TOP 1 [CTE_Level], [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
    FROM CTE
WHERE 1 = 1
    -- Hierarchical filter criteria
    AND 1 = [CTE_Level]
;

3. Use recursive query to construct the subordinate relationship path of data

/****** Construct the subordinate relationship path of data******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
    WHERE 1 = 1
        -- Fixed point query criteria
        AND tA.[ThisGUID] = '{F7D59F0A-5D2F-4F4A-98D3-7836A2EEE1D6}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union
    
    -- recursive query (Please note that)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], 
            CASE -- Membership path splicing
                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
        -- Recursive query condition(Please note that)
        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
;

 

Data operation (Part 1)

1. Batch update of conditional global data
Global: not limited to subordination

/****** Update the specified data when it is uncertain whether there is a specified data******/
WITH
CTE_01 ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT TOP 1 [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        --Expected condition
        AND 0 < CHARINDEX('x', [Information])
        AND [Version] = '1.0.0.1'

    UNION ALL -- union
     
     --'disappointment'result
    SELECT NULL, NULL, NULL, NULL, NULL, NULL
)
, -- This comma is essential
CTE_02 ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT TOP 1 [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        --Search from desired data set
        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'
;

2. Batch update of unconditional local range data

Local: limited to subordination

/****** Unify a field value of the data under a certain data******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
        -- Fixed point query criteria
    WHERE 1 = 1
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.0'

    UNION ALL -- union
    
    -- recursive query (Please note that)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version]
            --Unify a field value(Please note that)
            , tB.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- Recursive query condition(Please note that)
        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
;

 

Data operation (Part 2)

1. Delete subordinate relationship data

/****** Delete a data and its subordinate data in the tree structure table******/
WITH CTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    -- Fixed point query
    SELECT tA.[AIID], tA.[ThisGUID], tA.[ParentGUID], tA.[Information], tA.[Version], tA.[Reserved]
        FROM tTreeList AS tA
        -- Fixed point query criteria
    WHERE 1 = 1
        AND tA.[ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND tA.[Version] = '1.0.0.1'

    UNION ALL -- union
    
    -- recursive query (Please note that)
    SELECT tC.[AIID], tC.[ThisGUID], tC.[ParentGUID], tC.[Information], tC.[Version], tC.[Reserved]
        FROM CTE AS tB
    INNER JOIN tTreeList AS tC
        -- Recursive query condition(Please note that)
        ON tB.[ThisGUID] = tC.[ParentGUID]
            AND tC.[Version] = '1.0.0.1'
    WHERE 1 = 1
)
--You can
DELETE tTreeList
    FROM tTreeList AS tD
JOIN CTE AS tE
    ON tD.[ThisGUID] = tE.[ThisGUID] 
WHERE 1 = 1
;
--You can do the same
DELETE FROM tTreeList
    WHERE 1 = 1
        AND [ThisGUID] IN (
            SELECT [ThisGUID]
                FROM CTE
            WHERE 1 = 1
        )
;
--But never(Catastrophic error)
DELETE FROM CTE
WHERE 1 = 1
;

2. Delete and repair subordinate relationship data

/****** Delete a data in the tree structure table (the child data of the deleted data is automatically attached to the parent data of the deleted data)******/
WITH
tParentCTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS (
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        -- Locate the parent data of the deleted data
        AND [ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND [Version] = '1.0.0.1'
)
, -- This comma is essential
tChildCTE ([AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
) AS ( 
    SELECT [AIID], [ThisGUID], [ParentGUID], [Information], [Version], [Reserved]
        FROM tTreeList
    WHERE 1 = 1
        -- Locate sub data of deleted data
        AND [ParentGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
        AND [Version] = '1.0.0.1'
)
--Repair affiliation
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
    --Delete this data
    AND [ThisGUID] = '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'
;

 

Table structure operation is as follows

/****** Delete if table already exists******/
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******/
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
;

/****** Add table field comments******/
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Self increasing 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'book 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'father 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'information', @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'edition', @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'reserve', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tTreeList', @level2type=N'COLUMN', @level2name=N'Reserved'
GO
;

/****** Batch add data******/
INSERT INTO tTreeList([ThisGUID], [ParentGUID], [Information], [Version], [Reserved])
SELECT [ThisGUID], [ParentGUID], [Information], [Version], [Reserved] from tTreeList--Bak
WHERE 1 = 1
;

/****** Check data******/
SELECT * FROM tTreeList
;

There are many reasons why the GUID field of character type is used as the reference of data membership relationship instead of the self increasing integer field:
On the one hand, there may be N to 1, that is, multiple parents at the same level and one child
On the other hand, data may move in and out of different databases
What else will happen?

Of course, there are many disadvantages in doing so. Things are multifaceted
One of the more serious problems is that when the breadth and depth of data relations in the structure can not be underestimated, a slow word is not enough to describe the query operation
A mild solution is to create indexes for related fields, and build the following indexes for the tTreeList table

/****** Create index******/
CREATE UNIQUE INDEX uiThisGUID ON tTreeList([ThisGUID])
CREATE NONCLUSTERED INDEX nciParentGUID ON tTreeList([ParentGUID])

 

Keywords: T-SQL

Added by ferronrsmith on Wed, 26 Jan 2022 16:16:56 +0200

Popular Keywords

©2024 Programming VIP