declare @ClickID int =0; select @ClickID=CAST( RAND()*4 as int)+1; --drop table #TestData; with TestData(id,pid,name,type,date) as ( select cast(1 as int),cast(0 as int),'A','Dir',getdate() union all select 2,1,'B','Dir',getdate() union all select 3,1,'C','Dir',getdate() union all select 4,3,'D','Dir',getdate() union all select 5,1,'X.TXT','File',getdate() union all select 6,2,'L.HTML','File',getdate() union all select 7,2,'XZ.HTML','File',getdate() union all select 8,3,'CX.TXT','File',getdate() union all select 9,4,'HELLO.CPP','File',getdate() ) select * into #TestData from TestData; select * from #TestData where id=@ClickID;--显示点击的文件夹 with cte as ( select *,1 lvl from #TestData where id=@ClickID union all select t.*,cte.lvl+1 from #TestData t join cte on cte.id=t.pid ) select * from cte where lvl=2--显示所点文件夹内的内容
select @ClickID=CAST( RAND()*4 as int)+1;
--drop table #TestData;
with TestData(id,pid,name,type,date) as (
select cast(1 as int),cast(0 as int),'A','Dir',getdate() union all
select 2,1,'B','Dir',getdate() union all
select 3,1,'C','Dir',getdate() union all
select 4,3,'D','Dir',getdate() union all
select 5,1,'X.TXT','File',getdate() union all
select 6,2,'L.HTML','File',getdate() union all
select 7,2,'XZ.HTML','File',getdate() union all
select 8,3,'CX.TXT','File',getdate() union all
select 9,4,'HELLO.CPP','File',getdate()
)
select * into #TestData from TestData;
select * from #TestData where id=@ClickID;--显示点击的文件夹
with cte as (
select *,1 lvl
from #TestData where id=@ClickID
union all
select t.*,cte.lvl+1
from #TestData t
join cte on cte.id=t.pid
)
select * from cte where lvl=2--显示所点文件夹内的内容