时间戳

首页 > 解决方案 > 如何过滤递归 CTE SQL 查询

sql-server - 如何过滤递归 CTE SQL 查询

问题描述

我有一个递归 SQL 查询，它非常适合我想要它做的事情，但是我不确定我应该如何过滤它以便按字母顺序过滤的分支。例如：

UniqueID | Description | ParentID | MaterializedPath
1          CCC           0          0x00000001
2          BBB           0          0x00000002
3          AAA           0          0x00000003
4          CCCC          1          0x0000000100000004
5          BBBB          1          0x0000000100000005
6          AAAA          2          0x0000000200000006
7          DDDD          2          0x0000000200000007
8          EEEEE         4          0x000000010000000400000008
9          AAAAA         4          0x000000010000000400000009
10         CCCCC         6          0x00000002000000060000000A
11         BBBBB         6          0x00000002000000060000000B
12         FFFFFF        8          0x0000000100000004000000080000000C
13         BBBBBB        8          0x0000000100000004000000080000000D
14         GGGGGG        10         0x00000002000000060000000A0000000E
15         DDDDDD        10         0x00000002000000060000000A0000000F

回报：

UniqueID | Description | ParentID | MaterializedPath
1          CCC           0          0x00000001
4          CCCC          1          0x0000000100000004
8          EEEEE         4          0x000000010000000400000008
12         FFFFFF        8          0x0000000100000004000000080000000C
13         BBBBBB        8          0x0000000100000004000000080000000D
9          AAAAA         4          0x000000010000000400000009
5          BBBB          1          0x0000000100000005
2          BBB           0          0x00000002
6          AAAA          2          0x0000000200000006
10         CCCCC         6          0x00000002000000060000000A
14         GGGGGG        10         0x00000002000000060000000A0000000E
15         DDDDDD        10         0x00000002000000060000000A0000000F
11         BBBBB         6          0x00000002000000060000000B
7          DDDD          2          0x0000000200000007
3          AAA           0          0x00000003

但我实际上想要它做的是按描述对每个分支进行排序，以便它像这样返回。这样每个级别都按每个分支内的描述进行排序，因此对没有 parentID 的那些进行排序，然后对该分支的子项进行排序，依此类推。例如 A |-A |-A |-B |-C |-B |-C B C

所以我上面的数据会像这样返回。

UniqueID | Description | ParentID | MaterializedPath
3          AAA           0          0x00000003
2          BBB           0          0x00000002
6          AAAA          2          0x0000000200000006
11         BBBBB         6          0x00000002000000060000000B
10         CCCCC         6          0x00000002000000060000000A
15         DDDDDD        10         0x00000002000000060000000A0000000F
14         GGGGGG        10         0x00000002000000060000000A0000000E
7          DDDD          2          0x0000000200000007
1          CCC           0          0x00000001
5          BBBB          1          0x0000000100000005
4          CCCC          1          0x0000000100000004
9          AAAAA         4          0x000000010000000400000009
8          EEEEE         4          0x000000010000000400000008
13         BBBBBB        8          0x0000000100000004000000080000000D
12         FFFFFF        8          0x0000000100000004000000080000000C

这是我的 SQL 查询：

WITH T AS
(
    SELECT
        N.UniqueID, N.DESCRIPTION, N.ParentID, CAST(N.UniqueID AS varbinary(512)) AS MaterializedPath
    FROM
        TEMPTABLE1 N
    WHERE
        N.ParentID = 0

    UNION ALL

    SELECT
        N.UniqueID, N.DESCRIPTION, N.ParentID, CAST( T.MaterializedPath + CAST(N.UniqueID AS binary(4)) AS varbinary(512) ) AS MaterializedPath
    FROM
        TEMPTABLE1 N INNER JOIN T
            ON N.ParentID = T.UniqueID
)
SELECT *
FROM T
ORDER BY T.MaterializedPath

示例表设置：

CREATE TABLE TEMPTABLE1 (
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
Description NVARCHAR(10) NULL,
ParentID Int NULL
)
GO
INSERT INTO TEMPTABLE1 VALUES ('CCC', 0)
INSERT INTO TEMPTABLE1 VALUES ('BBB', 0)
INSERT INTO TEMPTABLE1 VALUES ('AAA', 0)
INSERT INTO TEMPTABLE1 VALUES ('CCCC', 1)
INSERT INTO TEMPTABLE1 VALUES ('BBBB', 1)
INSERT INTO TEMPTABLE1 VALUES ('AAAA', 2)
INSERT INTO TEMPTABLE1 VALUES ('DDDD', 2)
INSERT INTO TEMPTABLE1 VALUES ('EEEEE', 4)
INSERT INTO TEMPTABLE1 VALUES ('AAAAA', 4)
INSERT INTO TEMPTABLE1 VALUES ('CCCCC', 6)
INSERT INTO TEMPTABLE1 VALUES ('BBBBB', 6)
INSERT INTO TEMPTABLE1 VALUES ('FFFFFF', 8)
INSERT INTO TEMPTABLE1 VALUES ('BBBBBB', 8)
INSERT INTO TEMPTABLE1 VALUES ('GGGGGG', 10)
INSERT INTO TEMPTABLE1 VALUES ('DDDDDD', 10)

标签： sql-servertsql

解决方案

您可以使用多个列进行排序。在您的情况下，它将是： 

with T AS(...)
SELECT *
FROM T
ORDER BY T.ParentID, T.DESCRIPTION

推荐阅读