如何使用递归获取sql中的所有上升和后代行 [英] how to get the all ascendants and descendants rows in sql with recursion

问题描述

我在ms sql服务器表名称类别中有以下表数据

问题是我想获取祖父母，父母，兄弟姐妹，孩子，子孩子和自我的数据.

我希望你明白我的意思，如果我的要求需要更多说明，我可以编辑我的问题，只需在下面发表评论.

就我的尝试而言，我搜索了stackoverflow，并发现了许多使用父级获取完整分层数据的示例，但没有找到与传递子级以及获取父级，子级子级和自我相关的任何东西.

我也愿意接受使用 linq 为我提供解决方案的解决方案，因为这样我就可以在Category中获取完整的数据，并可以在.cs页面上使用linq.

如果我通过的是 heritage CategoryId 的 7 ，则查询应返回以下行

解决方案

答案是使用可追溯的公用表表达式"或CTE.这使您可以构建层次结构.下面是一个示例，根据此页面进行了修改，以匹配您的结构: http://msdn.microsoft.com/en-us/library/ms186243.aspx

 具有类别结构(ParentCategoryID，CategoryID，描述，状态，级别)作为(-锚成员定义选择c.ParentCategoryID，c.CategoryID，c.Description，c.Status，0 AS等级来自AS类别在哪里c.ParentCategoryID = 0全联盟-递归成员定义选择c.ParentCategoryID，c.CategoryID，c.Description，c.Status，等级+ 1来自AS类别INNER JOIN类别结构化为c_parent开启c.ParentCategoryID = c_parent.CategoryID)-执行CTE的语句选择不同的cs.ParentCategoryID，cs.CategoryID，cs.Description，cs.Status，cs.Level从类别结构化的CS，(从CategoryStructured WHERE(categoryID = 4)或(level = 1 AND parentCategoryID = 4)中选择类别的SELECT级别，ParentCategoryID，CategoryID)作为此类别在thisCategory.level-1和thisCategory.level + 1之间的cs.levelAND((thisCategory.level！= 0 AND cs.ParentCategoryID = thisCategory.ParentCategoryID)或cs.categoryID = thisCategory.ParentCategoryID或cs.ParentCategoryID = thisCategory.CategoryID或cs.CategoryID = thisCategory.CategoryID) 

已更新以反映您更新的问题.

edit 我知道您可以通过添加一些独特的内容使上述内容基本为您工作，但是我想出了一种更好的方式来解决此问题:

 具有类别结构(ParentCategoryID，CategoryID，描述，状态，级别)作为(-锚成员定义选择c.ParentCategoryID，c.CategoryID，c.Description，c.Status，0 AS等级来自AS类别在哪里(c.ParentCategoryID IS NULL AND c.categoryID = 7) -- 当 7 是顶级类别时，则它是根级别或(c.categoryID =(从类别c2中选择c2.parentCategoryID到c2.categoryID = 7))-当7是某个非顶级类别时，则7的父级是根全联盟-递归成员定义选择c.ParentCategoryID，c.CategoryID，c.Description，c.Status，等级+ 1来自AS类别INNER JOIN类别结构化为c_parent开启c.ParentCategoryID = c_parent.CategoryID)-执行CTE的语句选择cs.ParentCategoryID，cs.CategoryID，cs.Description，cs.Status，cs.Level从类别结构化CScs.level<3按cs.level排序 

i have the following table data in ms sql server table name category

and the problem is that i want to get the data of grand parent,parent,siblings,child and sub child and self.

i hope you get my point , if need any more clarification from my requirement i can edit my question just put a comment below.

as far about my try i searched stackoverflow and i found a lot of examples of getting full hierarchical data using parent but don't find anything which is related to pass the child and get parent, sub child and self.

I am also open for the solution which provide me solution using linq because then i can take complete data in Category and can use linq their on .cs page.

Edit : if i pass the 7 which is heritage CategoryId then the query should returns the following rows

解决方案

The answer is to use a recusive "Common Table Expression", or CTE. This allows you to build the structure of your hierarchy. Below is an example, modified to match your structure, based on this page: http://msdn.microsoft.com/en-us/library/ms186243.aspx

WITH CategoryStructured (ParentCategoryID, CategoryID, Description, Status, Level) 
AS 
( 
-- Anchor member definition 
SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 
0 AS Level 
FROM Category AS c 
WHERE c.ParentCategoryID=0 
UNION ALL 
-- Recursive member definition 
SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 
Level + 1 
FROM Category AS c 

INNER JOIN CategoryStructured AS c_parent 
ON c.ParentCategoryID = c_parent.CategoryID 
) 
-- Statement that executes the CTE 
SELECT distinct cs.ParentCategoryID, cs.CategoryID, cs.Description, cs.Status, cs.Level 
FROM 
CategoryStructured cs, 


(SELECT level,ParentCategoryID,CategoryID from CategoryStructured WHERE (categoryID = 4) OR (level = 1 AND parentCategoryID = 4)) as thisCategory 


WHERE cs.level BETWEEN thisCategory.level - 1 AND thisCategory.level+1 
AND ((thisCategory.level != 0 AND cs.ParentCategoryID = thisCategory.ParentCategoryID) 
OR cs.categoryID = thisCategory.ParentCategoryID 
OR cs.ParentCategoryID = thisCategory.CategoryID 
OR cs.CategoryID = thisCategory.CategoryID)

Updated to reflect your updated question.

edit I know you were able to get the above basically working for you with the added distinct, but I thought of a better way to handle this after I left chat:

WITH CategoryStructured (ParentCategoryID, CategoryID, Description, Status, Level)
AS
(
-- Anchor member definition
    SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 
        0 AS Level
    FROM Categories AS c
    WHERE 
     (c.ParentCategoryID IS NULL AND c.categoryID = 7) -- when 7 is a top level category, then it is the root level
     OR (c.categoryID = (SELECT c2.parentCategoryID FROM Categories c2 WHERE c2.categoryID = 7)) -- when 7 is some non-top level category, then 7's parent is the root
    UNION ALL
-- Recursive member definition
    SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 
        Level + 1
    FROM Categories AS c

    INNER JOIN CategoryStructured AS c_parent
        ON c.ParentCategoryID = c_parent.CategoryID
)
-- Statement that executes the CTE
SELECT cs.ParentCategoryID, cs.CategoryID, cs.Description, cs.Status, cs.Level
FROM 
  CategoryStructured cs
WHERE cs.level < 3
ORDER BY cs.level

这篇关于如何使用递归获取sql中的所有上升和后代行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！