sql自连接?该怎么处理

sql自连接?

select * from t_br t where exists(select * from t_br where id= t.superior_id )



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

输出1和3的记录？

[解决办法]

SQL code

declare @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 t where exists(select * from @table where id= t.superior_id )--#1.第一行记录superior_id=1, 当它从@table表中找id=1时, 没找到，所以第一行记录被过滤了--#2.同上--#3.第三行记录superior_id=266, 当它从@table表中找id=266时, 发现存在一条这样的记录，所以被返回。/*id    name    a    superior_id503    石家庄    345    266*/
[解决办法]
沒看明白什麼意思，按你寫的語句，應只能輸出
503 石家庄 345 266 
這一倏記錄吧
[解决办法]
SQL code
create table tb(id int, name nvarchar(64), a varchar(32),superior_id int)insert tb select 266,'河北','002',1 union all select 282,'长沙','001',1  union allselect 503,'石家庄','345',266 select * from tb where  id in (select superior_id from tb t where exists(select 1 from tb where id= t.superior_id ))union select * from tb t where exists(select 1 from tb where id= t.superior_id )/*id    name    a    superior_id266    河北    002    1503    石家庄    345    266*/drop table tb
[解决办法]
SQL code
select * from tab t where exists(select * from tab where id= t.superior_id )
[解决办法]
select * from @table t where exists(select 1 from @table where t.id = superior_id )

union all

select * from @table t where exists(select 1 from @table where id = t.superior_id )

[解决办法]
用了CTE查询，方便点：
SQL code
create table tb(id int, name nvarchar(64), a varchar(32),superior_id int)insert tb select 266,N'河北','002',1 union all select 282,N'长沙','001',1  union allselect 503,N'石家庄','345',266 union allselect 509,N'邯郸','645',503 with t(id,name,superior_id,IsChild)as(select id,name,superior_id,0 as IsChild from tbwhere superior_id=1union allselect tb.id,tb.name,tb.superior_id,t.IsChild+1 as IsChild from tbjoin t on tb.superior_id=t.idwhere tb.superior_id!=1)select * from tbwhere (id in (select superior_id from t where isChild!=0) and superior_id=1) or (id in (select id from t where isChild!=0))