用于从父子关系中获取分层值的SQL查询 [英] SQL Query for fetching Hierarchical value from Parent Child Relationship

问题描述

嗨Frnds，



我有一个多层次的桌子作为子女关系。



For例如：

Hi Frnds,

I have a table with multiple Hierarchy as child relationship.

For eg .:

ParentId   ChildID

    1             11
    11            21
    21            31
    31            41
    41            51
    51            61
    61            71

我需要根据ChildId输出和Hierarchy级别...

例如，如果我将查询中的值传递给Chi ldId = 61和Hierachy = 2



输出应该是

I need output as per ChildId and level of Hierarchy...
For example if i pass values in query as ChildId=61 and Hierachy =2

Output Should be

ParentId    ChildId

   31           41

例如，如果我在查询中传递值为ChildId = 61并且Hierachy = 3



输出应该是

For example if i pass values in query as ChildId=61 and Hierachy =3

Output Should be

ParentId    ChildId

  21           31

我尝试了不同的方法，但我得到了解决方案。帮我解决问题。





谢谢

Suresh

I tried different ways , but i ddnt get solution.Help me for get solution.


Thanks
Suresh

推荐答案

坚韧我对下面的解决方案不满意但是这可以让你去。

Tough i am not happy with below solution but this could get you going.

DECLARE @TBL TABLE
(
	PARENTID INT,
	CHILDID INT
)

DECLARE @TAGERTTBL TABLE
(
	PARENTID INT,
	CHILDID INT,
	Level int IDENTITY(0, 1)
)

INSERT INTO @TBL(PARENTID, CHILDID)
SELECT    1,			 11	UNION ALL
SELECT    11,            21 UNION ALL
SELECT    21,            31 UNION ALL
SELECT    31,            41 UNION ALL
SELECT    41,            51 UNION ALL
SELECT    51,            61 UNION ALL
SELECT    61,            71;

with TMPH(ParentId, ChildID, Level)
AS (
	SELECT ParentID, CHildID, 0 AS level
	FROM   @TBL
	WHERE  PARENTID = 61
	UNION ALL
	SELECT T.ParentId, T.ChildID, Level + 1
	FROM TMPH TM 
	INNER JOIN @TBL T ON TM.ParentId = T.ChildID 
)
INSERT INTO @TAGERTTBL(PARENTID, CHILDID)
SELECT PARENTID, CHILDID FROM TMPH
order by Level desc

SELECT * 
FROM @TAGERTTBL
where Level in (2,3 )

这篇关于用于从父子关系中获取分层值的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！