Tree-type data processing example in SQL Server 2005-1

server| Sample | data

Example of tree data processing in SQL Server 2005
--Create test data
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [TB] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [TB]
Go

--Sample data
CREATE TABLE [TB] ([id] int PRIMARY key,[pid] int,name nvarchar (20))
INSERT [TB] SELECT 1,0,n ' China '
UNION all SELECT 2,0,n ' America '
UNION all SELECT 3,0,n ' Canada '
UNION all SELECT 4,1,n ' Beijing '
UNION all SELECT 5,1,n ' Shanghai '
UNION all SELECT 6,1,n ' Jiangsu '
UNION all SELECT 7,6,n ' Suzhou '
UNION all SELECT 8,7,n ' Changshu '
UNION all SELECT 9,6,n ' Nanjing '
UNION all SELECT 10,6,n ' Wuxi '
UNION all SELECT 11,2,n ' New York '
UNION all SELECT 12,2,n ' San Francisco '
Go

--Queries all children of the specified 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
-- =====================================================
--Queries all children of the specified ID
--Jiangjian 2005-07 (please keep this information for reference)

--Invoke the example

/*--Call (query all children)
SELECT a.*, Level =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
--If only the most detailed child is displayed (there are no children below), the above sentence is changed to the following
--SELECT * from CTB A
--WHERE not EXISTS (
--SELECT 1 from [TB] WHERE [Pid]=a.[id])
)
Go

--Call (query all children)
SELECT a.*, Level =b.[level]
From [TB] a,f_cid (2) B
WHERE A.[id]=b.[id]
Go

--Queries all parent of the specified 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
-- =====================================================
--Queries all parent of the specified ID
--Jiangjian 2005-07 (please keep this information for reference)

--Invoke the example

/*--Call (query all parent)
SELECT a.*, Level =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

--Call (query all parent)
SELECT a.*, Level =b.[level]
From [TB] a,[f_pid] (7) B
WHERE A.[id]=b.[id]
Go

--Tree-type outlining

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
-- =====================================================
--Level and sort fields (tree outline)
--Jiangjian 2005-07 (please keep this information for reference)

--Invoke the example

/*--Call implements tree display

--calling a function to implement a hierarchical display
SELECT N ' | ' +replicate ('-', b.[level]*4) +a.name
From [TB] a,f_id () B
WHERE A.[id]=b.[id]
ORDER BY B.sid

Of course, this also can not write functions at all, direct sorting can
With STB ([Id],[level],[sid])
As (
SELECT [Id],1,cast (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 (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

--calling a function to implement a hierarchical display
SELECT N ' | ' +replicate ('-', b.[level]*4) +a.name
From [TB] a,f_id () B
WHERE A.[id]=b.[id]
ORDER BY B.sid
Go