我必须在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 |
解决方法
好吧,足够的脑细胞已经死了.
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];
