sql-server – 父子树分层ORDER

我必需在SQL Server 2008 R2中存眷数据. SQLFiddle

架构：

CREATE TABLE [dbo].[ICFilters](
   [ICFilterID] [int] IDENTITY(1,1) NOT NULL,[ParentID] [int] NOT NULL DEFAULT 0,[FilterDesc] [varchar](50) NOT NULL,[Active] [tinyint] NOT NULL DEFAULT 1,CONSTRAINT [PK_ICFilters] PRIMARY KEY CLUSTERED 
 ( [ICFilterID] ASC ) WITH 
    PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON
 ) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[ICFilters] (ParentID,FilterDesc,Active)
Values 
(0,'Product Type',1),(1,'ProdSubType_1','ProdSubType_2','ProdSubType_3','ProdSubType_4',(2,'PST_1.1','PST_1.2','PST_1.3','PST_1.4','PST_1.5','PST_1.6','PST_1.7',0),(3,'PST_2.1','PST_2.2','PST_2.3','PST_2.4',(14,'PST_2.2.1','PST_2.2.2','PST_2.2.3','PST_2.8',1)

表：

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          3 |        1 | ProdSubType_2 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         20 |        3 |       PST_2.8 |      1 |

每一行都有其父级的ID,而root的parentid = 0. FilterDescs只是示例描写,以是我不能实行理会那些用于排序.

题目

是否可以以树状方法选择全部行？假如是这样,怎么样？
当我说’树状’时,我的意思是递归地选择父项后跟其全部子项,然后选择个中每一项的全部子项,依此类推.深度第一个树遍历.

我的伴侣和我已经实行过,但我们没有事变办理方案,但会继承全力.我对sql相等新,以是大概这可以轻松完成,我只是让工作变得越发坚苦.

示例(所需)输出：

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|          3 |        1 | ProdSubType_2 |      1 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         20 |        3 |       PST_2.8 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |

办理要领

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID],[ParentID],[FilterDesc],[Active],CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID],i.[ParentID],i.[FilterDesc],i.[Active],Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID],[Active]
FROM cte
ORDER BY [Level];

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!