SQL 利用递归查询数据,该如何解决

SQL 利用递归查询数据
有表结构 id，parentID 。返回在这一个组的全部数据，以及所在的层数

如  1，0
      5， 1
      9，5
     10, 2
上面的结果，不管输入1,5,9
最后都返回     1     1
                         5     2
                         9      3
(1,5,9都在一个组里面的，而10是在2的组里面的)

因为9的上级是5(深度是3)，5的上级是1（深度是2）,1的父级是0(0表示最高级了，深度是1)

------解决思路----------------------

DECLARE @In INT
SET @In=9
;WITH CTE AS(
	SELECT id,parentID,0 D FROM TB WHERE id=@In
	UNION ALL
	SELECT T1.id,T1.parentID,T2.D-1
	FROM TB T1
		JOIN CTE T2 ON T1.id=T2.parentID
)
,CTE2 AS(
	SELECT id,parentID,0 D FROM TB WHERE parentID=@In
	UNION ALL
	SELECT T1.id,T1.parentID,T2.D+1
	FROM TB T1
		JOIN CTE2 T2 ON T1.parentID=T2.id
)
,CTE3 AS(
	SELECT * FROM CTE
	UNION ALL
	SELECT * FROM CTE2
)
SELECT T1.id,T1.D-T2.D+1 DEEP
FROM CTE3 T1
	CROSS JOIN(SELECT MIN(D)D FROM CTE)T2
ORDER BY DEEP