选择同一表关系中的所有父级或子级SQL Server

我已经遇到了这个问题by通过这种方式解决了问题

 --all  "parent + grandparent + etc" @childID Replaced with the ID you needwith tbParent as(   select * from Elem where [KEY]=@childID   union all   select Elem.* from Elem  join tbParent  on Elem.[KEY]=tbParent.PARENT_KEY) SELECt * FROM  tbParent --all "sons + grandsons + etc" @parentID Replaced with the ID you needwith tbsons as(  select * from Elem where [KEY]=@parentID  union all  select Elem.* from Elem  join tbsons  on Elem.PARENT_KEY=tbsons.[KEY])SELECt * FROM tbsons

PS。我的英语不好。