SQL Server 2005 中的樹形資料處理樣本-1

server|樣本|資料

SQL Server 2005 中的樹形資料處理樣本
-- 建立測試資料
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [tb]
GO

-- 樣本資料
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(引用請保留此資訊)

-- 調用樣本

/*--調用(查詢所有的子)
    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]=@id
        UNION ALL
        SELECT A.[id],B.[level]+1
        FROM [tb] A,ctb B
        WHERE A.[pid]=B.[id])
    SELECT * FROM ctb
    --如果只顯示最明細的子(下面沒有子),則將上面這句改為下面的
--    SELECT * FROM ctb A
--    WHERE NOT EXISTS(
--        SELECT 1 FROM [tb] WHERE [pid]=A.[id])
)
GO

--調用(查詢所有的子)
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(引用請保留此資訊)

-- 調用樣本

/*--調用(查詢所有的父)
    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]=@id
            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

--調用(查詢所有的父)
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(引用請保留此資訊)

-- 調用樣本

/*--調用實現樹形顯示

    --調用函數實現分級顯示
    SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
    FROM [tb] A,f_id()B
    WHERE a.[id]=b.[id]
    ORDER BY b.sid

    --當然,這個也可以根本不用寫函數,直接排序即可
    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

--調用函數實現分級顯示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO