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

标题: [教学]SQL Server从子节点取出所有的父节点，select all parent node [打印本页]
作者: 资讯王    时间: 2012-7-4 20:24
标题: [教学]SQL Server从子节点取出所有的父节点，select all parent node
[教学]SQL Server从子节点取出所有的父节点，select all parent node from child node

Sample 1 Order By Depth
  1. DECLARE @ItemCategory TABLE
  2. (
  3.     ChildId INT NOT NULL,
  4.     Name varchar(50) NOT NULL,
  5.     ParentId int NULL
  6. )

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

  20. DECLARE @ChildID INT = 11

  22. ;WITH c
  23. AS
  24. (
  25.     SELECT ChildId, ParentId, Name, 1 AS Depth
  26.     FROM @ItemCategory
  27.     WHERE ChildId = @ChildID

  29.     UNION ALL

  31.     SELECT t.ChildId, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
  32.     FROM @ItemCategory T  
  33.     INNER JOIN c ON t.ChildId = c.ParentId
  34. )
  35. SELECT *
  36. FROM c
复制代码
Sample 2 Order By Depth DESC
  1. DECLARE @ItemCategory TABLE
  2. (
  3.     ChildId INT NOT NULL,
  4.     Name varchar(50) NOT NULL,
  5.     ParentId int NULL
  6. )

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

  20. DECLARE @ChildID INT = 11

  22. ;WITH c
  23. AS
  24. (
  25.     SELECT ChildID, ParentId, Name, 1 AS Depth
  26.     FROM @ItemCategory
  27.     WHERE ChildID = @ChildID

  29.     UNION ALL

  31.     SELECT t.ChildID, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
  32.     FROM @ItemCategory T  
  33.     INNER JOIN c ON t.ChildID = c.ParentId
  34. )
  35. SELECT ChildID,
  36.        ParentID,
  37.        Name,
  38.        MAX(Depth) OVER() - Depth + 1 AS InverseDepth
  39. FROM c
复制代码




欢迎光临 大马资讯论坛 - 马来西亚中文资讯平台 (http://freeinfo.com.my/) Powered by Discuz! X3.3