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的记录?
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, 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_id 503 石家庄 345 266 */
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 all select 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_id 266 河北 002 1 503 石家庄 345 266 */ drop table tb
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 allselect * from @table t where exists(select 1 from @table where id = t.superior_id )
id name a superior_id 266 河北 002 1 282 长沙 001 1 503 石家庄 345 266 输出 266 河北 002 1 503 石家庄 345 266
用了CTE查询,方便点: 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 all select 503,N'石家庄','345',266 union all select 509,N'邯郸','645',503 with t(id,name,superior_id,IsChild) as ( select id,name,superior_id,0 as IsChild from tb where superior_id=1 union all select tb.id,tb.name,tb.superior_id,t.IsChild+1 as IsChild from tb join t on tb.superior_id=t.id where tb.superior_id!=1 )select * from tb where (id in (select superior_id from t where isChild!=0) and superior_id=1) or (id in (select id from t where isChild!=0))
(
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, 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_id
503 石家庄 345 266
*/
insert tb
select 266,'河北','002',1 union all
select 282,'长沙','001',1 union all
select 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_id
266 河北 002 1
503 石家庄 345 266
*/
drop table tb
266 河北 002 1
282 长沙 001 1
503 石家庄 345 266
输出
266 河北 002 1
503 石家庄 345 266
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 all
select 503,N'石家庄','345',266 union all
select 509,N'邯郸','645',503
with t(id,name,superior_id,IsChild)
as
(
select id,name,superior_id,0 as IsChild from tb
where superior_id=1
union all
select tb.id,tb.name,tb.superior_id,t.IsChild+1 as IsChild from tb
join t on tb.superior_id=t.id
where tb.superior_id!=1
)select * from tb
where (id in (select superior_id from t where isChild!=0) and superior_id=1)
or (id in (select id from t where isChild!=0))