create table #lfy(id int,data varchar(20),father int,tag char(1))
go
insert into #lfy
select 1,'a',0,'1'
union all
select 2,'b',0,'1'
union all
select 3,'c',0,'1'
union all
select 11,'aa',1,'0'
union all
select 12,'ab',1,'0'
union all
select 13,'ac',1,'0'
union all
select 21,'ba',2,'0'
union all
select 22,'bb',2,'0'
union all
select 31,'ca',3,'0'
union all
select 32,'cb',3,'0'
union all
select 111,'aaa',11,'1'
union all
select 112,'aab',11,'0'
union all
select 121,'aba',12,'0'
union all
select 122,'abb',12,'1'
--第一层father为0
--第二层tag='0'
--father为父层ID
select * from #lfy where father=0
union all
select * from #lfy where id in (select distinct father from #lfy where father>0 and tag='1')
union all
select * from #lfy where father>0 and tag='1'
select * from tablename a ---顶层数据全部显示
where exists (
select * from tablename
where 隶属=a.id
)
and isnull(隶属,0)=0
union all
select * from tablename a ---底层数据中只有“状态”为1显示;
where not exists (
select * from tablename
where 隶属=a.id
)
and isnull(隶属,0)<>0
and 状态=1
union all
select * from tablename a ---中间层数据中,若底层有数据显示,则也显示。
where exists (
select * from tablename
where 隶属=a.id
and 状态=1
)
and isnull(隶属,0)<>0