Sql server自联接树视图 [英] Sql server self join tree view

问题描述

我创建了具有自我关系的Products_Categories表，用户可以创建无限的子类别作为树。如何从数据库中检索数据到这样的照片的TreeView




截图1




截图2

I created Products_Categories table with self relations to users can create unlimited subcategories as tree. How can i retrieve data from database to TreeView like this photos


Screenshot 1


Screenshot 2

CREATE TABLE ProductsCategories(
    CategoryID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    Specifications nvarchar(250) NULL,
    ParentID int NULL,
    IsDeleted int NOT NULL,
 CONSTRAINT PK_ProductsCategories PRIMARY KEY CLUSTERED 
(
    CategoryID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY

GO
SET IDENTITY_INSERT ProductsCategories ON 

GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (9, N'Hardware', NULL, 9, 1)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (10, N'CPU', NULL, 9, 1)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (11, N'Accessories', NULL, 11, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (12, N'Network', NULL, 12, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (13, N'Intel', NULL, 10, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (14, N'AMD', NULL, 10, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (15, N'3.0/2M', NULL, 13, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (16, N'Athlon', NULL, 14, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (17, N'Mouse', NULL, 11, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (18, N'Mouse Premium', NULL, 17, 0)
GO
SET IDENTITY_INSERT ProductsCategories OFF
GO
ALTER TABLE ProductsCategories ADD  CONSTRAINT DF_ProductsCategories_IsDeleted  DEFAULT ((0)) FOR IsDeleted
GO
ALTER TABLE ProductsCategories  WITH CHECK ADD  CONSTRAINT FK_ProductsCategories_ProductsCategories FOREIGN KEY(ParentID)
REFERENCES ProductsCategories (CategoryID)
GO
ALTER TABLE ProductsCategories CHECK CONSTRAINT FK_ProductsCategories_ProductsCategories
GO

Select PC2.Name AS 'Parent Category',
        PC.Name AS 'Category',
        PC.Specifications
From ProductsCategories AS PC
Join ProductsCategories AS PC2
On PC.CategoryID = PC.ParentID

我的尝试：



我手动添加了TreeView数据，但我没有从数据库中检索它的想法

What I have tried:

I have added TreeView data manually but i have not ideas to retrieve it from database

推荐答案

为了使用单个查询获取所有级别，你需要使用递归查询。这可以通过Common Table Expressions（CTE）实现。



例如，看看使用公用表表达式的递归查询 [ ^ ]

In order to fetch all levels using a single query, you need to use recursive query. This can be achieved by Common Table Expressions (CTE).

For an example, have a look at Recursive Queries Using Common Table Expressions[^]

这篇关于Sql server自联接树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！