[Challenge] 找出從屬關係

Beyond Relational TSQL Beginners Challenge 9

This challenge refers to a real-world problem I came across recently. The requirement was to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

To give a better idea of the problem consider the following.

A(1)
      |
---------------------------------
|               |         |
B(2)            R(3)      C(4)
|                         |
--------------------      Child1OfC(9)
|               |
Child1OfB(5)    Child2OfB(6)
|
----------
|      |
D(7)   E(8) 

Given ChildId = 5 the Output will be A,B,R,C, Child1OfB, Child2OfB.Because A,B,R,C are all Parents of 5 i.e. Child1OfB in some way (direct or indirect) and Child2OfB is the adjacent of Child1OfB.However, the children of Child1OfB (if any is present) will not be consider as they are it’s children.

• Sample Data

ParentId ChildId Name
-------- ------- ------------------ 
NULL      1      Niladri Biswas
1         2      Piyush Ghosh
1         3      Agnish Basu
1         4      Deepak Kumar Goyal
2         5      Sachin Srivastav
2         6      Nishant Mandilwar
5         7      Arindam Pal
5         8      Mahi Sharma
3         9      Mahima Roy
3        10      Simran Motilal
9        11      Raj Malhotra
9        12      Sharmistha Roy
10       13      Preeti Sen
10       14      Holly Huggins

Given a @ChildId = 6 as Parameter,

• Expected Results

Id Name
--- -------------------------
1  Niladri Biswas
3     Agnish Basu
4     Deepak Kumar Goyal
2     Piyush Ghosh
6         Nishant Mandilwar
5         Sachin Srivastav

• Rules
1. The solution should work on SQL Server 2005 and above.
2. Column names should respect the desired output shown.
3. Output must be sorted in DESCENDING ORDER of Name.

• 個人解法

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );
INSERT INTO @ParentChild 
SELECT NULL,1,'Niladri Biswas' UNION ALL 
SELECT 1,2,'Piyush Ghosh' UNION ALL  
SELECT 1,3,'Agnish Basu' UNION ALL  
SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL
SELECT 2,5,'Sachin Srivastav' UNION ALL 
SELECT 2,6,'Nishant Mandilwar' UNION ALL 
SELECT 5,7,'Arindam Pal' UNION ALL 
SELECT 5,8,'Mahi Sharma' UNION ALL 
SELECT 3,9,'Mahima Roy' UNION ALL 
SELECT 3,10,'Simran Motilal' UNION ALL 
SELECT 9,11,'Raj Malhotra' UNION ALL
SELECT 9,12,'Sharmistha Roy' UNION ALL 
SELECT 10,13,'Preeti Sen'  UNION ALL 
SELECT 10,14,'Holly Huggins'

;
WITH CTE AS
(
  SELECT Parentid , Childid , [Name] , 
      CAST([Name] AS VARCHAR(100)) AS Tree , 
      1 AS lvl
  FROM @ParentChild
  WHERE Parentid IS NULL
  UNION ALL
  SELECT P.Parentid , P.Childid , P.[Name] , 
      CAST(REPLICATE(SPACE(5),lvl + 1) + P.[Name]  AS varchar(100)) ,
      lvl + 1
  FROM CTE AS T
     JOIN @ParentChild AS P ON T.ChildID = P.Parentid
)
SELECT Childid AS ID ,Tree AS Name
FROM
(
  SELECT * , 
      ROW_NUMBER() OVER (ORDER BY Parentid , lvl , [Name])  AS ROWNO
  FROM CTE
) AS T
WHERE T.ROWNO <= 6

參考資料：
• TSQL Beginners Challenge 9 - Find the Parents and Adjacent Members of the Node
• MSDN UNICODE