表的自连接查询？解决方案_Sql Server

表的自连接查询？
declare @table table
(
id int ,
name nvarchar(10),
a varchar(10),
superior_id int
)
insert @table
select 266, N'河北', 002, 1 union all
select 282, N'长沙', 001, 1 union all
select 503, N'石家庄', 345, 266

id name a superior_id
266 河北 002 1
282 长沙 001 1
503 石家庄 345 266

输出
266 河北 002 1
503 石家庄 345 266
查询出河北及所属的机构，关联字段为266？

上个帖子没说明白

------解决方案--------------------

SQL code

WITH CTE AS (SELECT * FROM @TABLE WHERE ID = 266UNION ALLSELECT A.* FROM @TABLE A,CTE AS TWHERE A.superior_id  = T.ID)SELECT * FROM CTE
------解决方案--------------------
SQL codedeclare @table table(  id int ,  name nvarchar(10),  a varchar(10),  superior_id int)insert @tableselect 266, N'河北', 002, 1 union allselect 282, N'长沙', 001, 1 union allselect 503, N'石家庄', 345, 266select * from @table where id = 266union select * from @table t where exists(select 1 from @table where id = t.superior_id and t.superior_id = 266)