[教学]SQL Server从父节点取出所有的子节点，select all child node
[教学]SQL Server从父节点取出所有的子节点，select all child node from parent nodeDECLARE @ItemCategory TABLE(
ChildId INT NOT NULL,
Name varchar(50) NOT NULL,
ParentId int NULL
)
INSERT INTO @ItemCategory SELECT 1, 'Toys', null
INSERT INTO @ItemCategory SELECT 2, 'Computers', null
INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
INSERT INTO @ItemCategory SELECT 6, 'Games', 1
INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10
DECLARE @ParentId INT = 1
;
with cte as (
select * from @ItemCategory where ParentId = @ParentId
union all
select T.*
from @ItemCategory T
inner join cteon cte.ChildId = T.ParentId
)
select *from cte
页:
[1]