随记

 

--drop table #tb
Create table #tb(id int,pid int,tag bit,name varchar(50))
insert into #tb 
select 1,0,0,'1' union all
select 2,1,0,'1-1' union all
select 3,1,0,'1-2' union all
select 4,1,0,'1-3' union all
select 5,4,0,'1-3-1' union all
select 6,5,0,'1-3-1-1' union all
select 7,0,0,'2' union all
select 8,7,0,'2-1' union all
select 9,8,0,'2-1-1' union all
select 10,7,0,'2-2' union all
select 11,0,0,'3' union all
select 12,0,0,'4' union all
select 13,12,1,'4-1' union all
select 14,13,0,'4-1-1' union all
select 15,13,0,'4-1-2'

select * from #tb
--1.查询没有子级的数据(id:2,3,6,9,10,11,14,15)
--2.查询没有子级的数据，并且父级tag为1，则查父级，不查询子级(id:2,3,6,9,10,11,13)
--3.查询有子级，并且pid为0的数据(id:1,4,7,8,11,12,13)


select * from #tb where id not in (select pid from #tb)

select * from #tb where id not in (select pid from #tb)
and id not in (select A.id from #tb A inner join #tb B on B.id = A.pid and B.tag = 1)
or id in (select A.pid from #tb A inner join #tb B on B.id = A.pid and B.tag = 1)

select * from #tb where id in (select pid from #tb) or pid = 0