sql server 2005 中的樹形數(shù)據(jù)處理示例
-- 創(chuàng)建測試數(shù)據(jù)
if exists (select * from dbo.sysobjects where id = object_id(n'[tb]') and objectproperty(id, n'isusertable') = 1)
    drop table [tb]
go

-- 示例數(shù)據(jù)
create table [tb]([id] int primary key,[pid] int,name nvarchar(20))
insert [tb] select  1,0,n'中國'
union  all  select  2,0,n'美國'
union  all  select  3,0,n'加拿大'
union  all  select  4,1,n'北京'
union  all  select  5,1,n'上海'
union  all  select  6,1,n'江蘇'
union  all  select  7,6,n'蘇州'
union  all  select  8,7,n'常熟'
union  all  select  9,6,n'南京'
union  all  select 10,6,n'無錫'
union  all  select 11,2,n'紐約'
union  all  select 12,2,n'舊金山'
go

-- 查詢指定id的所有子
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_cid]') and xtype in (n'fn', n'if', n'tf'))
drop function [dbo].[f_cid]
go
-- =====================================================
-- 查詢指定id的所有子
-- 鄒建 2005-07(引用請保留此信息)

-- 調(diào)用示例

/*--調(diào)用(查詢所有的子)
    select a.*,層次=b.[level]
    from [tb] a,f_cid(2)b
    where a.[id]=b.[id]
--*/
-- =====================================================
create function f_cid(@id int)
returns table
as
return(
    with ctb([id],[level])
    as(
        select [id],1 from [tb]
        where [pid][email protected]
        union all
        select a.[id],b.[level]+1
        from [tb] a,ctb b
        where a.[pid]=b.[id])
    select * from ctb
    --如果只顯示最明細(xì)的子(下面沒有子),則將上面這句改為下面的
--    select * from ctb a
--    where not exists(
--        select 1 from [tb] where [pid]=a.[id])
)
go

--調(diào)用(查詢所有的子)
select a.*,層次=b.[level]
from [tb] a,f_cid(2)b
where a.[id]=b.[id]
go

-- 查詢指定id的所有父
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_pid]') and xtype in (n'fn', n'if', n'tf'))
drop function [dbo].[f_pid]
go
-- =====================================================
-- 查詢指定id的所有父
-- 鄒建 2005-07(引用請保留此信息)

-- 調(diào)用示例

/*--調(diào)用(查詢所有的父)
    select a.*,層次=b.[level]
    from [tb] a,[f_pid](2)b
    where a.[id]=b.[id]
--*/
-- =====================================================
create function [f_pid](@id int)
returns table
as
return(
    with ptb([id],[level])
    as(
        select [pid],1 from [tb]
        where [id][email protected]
            and [pid]<>0
        union all
        select a.[pid],b.[level]+1
        from [tb] a,ptb b
        where a.[id]=b.[id]
            and [pid]<>0)
    select * from ptb
)
go

--調(diào)用(查詢所有的父)
select a.*,層次=b.[level]
from [tb] a,[f_pid](7)b
where a.[id]=b.[id]
go

-- 樹形分級顯示

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_id]') and xtype in (n'fn', n'if', n'tf'))
    drop function [dbo].[f_id]
go
-- =====================================================
-- 級別及排序字段(樹形分級顯示)
-- 鄒建 2005-07(引用請保留此信息)

-- 調(diào)用示例

/*--調(diào)用實(shí)現(xiàn)樹形顯示

    --調(diào)用函數(shù)實(shí)現(xiàn)分級顯示
    select n'|'+replicate('-',b.[level]*4)+a.name
    from [tb] a,f_id()b
    where a.[id]=b.[id]
    order by b.sid

    --當(dāng)然,這個(gè)也可以根本不用寫函數(shù),直接排序即可
    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   
--*/
-- =====================================================
create function f_id()
returns table
as
return(
    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 * from stb
)
go

--調(diào)用函數(shù)實(shí)現(xiàn)分級顯示
select n'|'+replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go