-- =====================================================
-- 直接查詢的應用實例
-- =====================================================

-- 1. 每個葉子結點的 fullname
with stb([id],[fullname],[pid],[flag])
as(
    select [id],cast(rtrim([name]) as nvarchar(4000)),[pid],1
    from [tb] a
    where not exists(
        select 1 from [tb]
        where [pid]=a.[id])
    union all
    select a.[id],rtrim(b.[name])+'/'+a.[fullname],b.[pid],a.flag+1
    from stb a,[tb] b
    where a.[pid]=b.[id])
select [id],[fullname] from stb a
where not exists(
    select * from stb
    where [id]=a.[id]
        and flag>a.flag)
order by [id]
go

-- 2. 每個結點的 fullname
with stb([id],[fullname],[pid],[flag])
as(
    select [id],cast(rtrim([name]) as nvarchar(4000)),[pid],1
    from [tb]
    union all
    select a.[id],rtrim(b.[name])+'/'+a.[fullname],b.[pid],a.flag+1
    from stb a,[tb] b
    where a.[pid]=b.[id])
select [id],[fullname] from stb a
where not exists(
    select * from stb
    where [id]=a.[id]
        and flag>a.flag)
order by [id]
go

-- 3. 樹形顯示數據
with stb([id],[level],[sid])
as(
    select [id],1,cast(right(10000+[id],4) as varchar(8000))
    from [tb]
    where [pid]=0
    union all
    select a.[id],b.[level]+1,b.sid+right(10000+a.[id],4)
    from [tb] a,stb b
    where a.[pid]=b.[id])
select n'|'+replicate('-',b.[level]*4)+a.name
from [tb] a,stb b
where a.[id]=b.[id]
order by b.sid   
go

-- 4. 檢查不規范的數據
with chktb([id],[pid],[level],[path],[flag])
as(
    select [id],[pid],1,
        cast([id] as varchar(8000)),
        case when [id]=[pid] then 1 else 0 end
    from [tb]
    union all
    select a.[id],b.[pid],b.[level]+1,
        cast(b.[path]+' > '+rtrim(a.[id]) as varchar(8000)),
        case when a.[id]=b.[pid] then 1 else 0 end
    from [tb] a,chktb b
    where a.[pid]=b.[id]
        and b.[flag]=0)
select * from chktb
where [flag]=1
order by [path]   
go

-- 5. 查詢結點的所有子結點數
with sumtb([id],[level])
as(
    select [pid],1
    from [tb] a
    where [pid]<>0
    union all
    select a.[pid],b.[level]+1
    from [tb] a,sumtb b
    where a.[id]=b.[id]
        and a.[pid]<>0)
select a.[id],childcounts=count(b.[id])
from [tb] a
    left join sumtb b
        on a.[id]=b.[id]
group by a.[id]
go

-- 6. 查詢結點的所有父結點數
with sumtb([id],[level],[parentcounts])
as(
    select [id],1,0
    from [tb] a
    where [pid]=0
    union all
    select a.[id],b.[level]+1,b.[parentcounts]+1
    from [tb] a,sumtb b
    where a.[pid]=b.[id])
select * from sumtb
order by [id]
go