大马资讯论坛 - 马来西亚中文资讯平台

 找回密码
 注册
搜索

[教学]SQL Server从父节点取出所有的子节点,select all child node

[复制链接]
发表于 2012-7-4 20:11:22 | 显示全部楼层 |阅读模式
[教学]SQL Server从父节点取出所有的子节点,select all child node from parent node
  1. DECLARE @ItemCategory TABLE
  2. (
  3.     ChildId INT NOT NULL,
  4.     Name varchar(50) NOT NULL,
  5.     ParentId int NULL
  6. )

  7. INSERT INTO @ItemCategory SELECT 1, 'Toys', null
  8. INSERT INTO @ItemCategory SELECT 2, 'Computers', null
  9. INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
  10. INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
  11. INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
  12. INSERT INTO @ItemCategory SELECT 6, 'Games', 1
  13. INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
  14. INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
  15. INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
  16. INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
  17. INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10

  18. DECLARE @ParentId INT = 1

  19. ;
  20. with cte as (
  21.         select * from @ItemCategory where ParentId = @ParentId
  22.         union all
  23.         select T.*
  24.         from @ItemCategory T
  25.         inner join cte  on cte.ChildId = T.ParentId
  26. )
  27. select *  from cte
复制代码

手机版|大马资讯论坛  

GMT+8, 2024-4-22 11:42 , Processed in 0.026714 second(s), 13 queries , File On.

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表