*
标题：查询各节点的父路径函数
*/
/*
原始数据及要求结果如下：
--食品
--水果
--香蕉
--苹果
--蔬菜
--青菜
id pid
name
----------- ----------- --------------------
1 0 食品
2 1 水果
3 1 蔬菜
4 2 香蕉
5 2 苹果
6 3 青菜
要求得到各节点的父路径即如下结果：
id pid
name
路径
--- --- ----- ---------------
1 0 食品 食品
2 1 水果 食品,水果
3 1 蔬菜 食品,蔬菜
4 2 香蕉 食品,水果,香蕉
5 2 苹果 食品,水果,苹果
6 3 青菜 食品,蔬菜,青菜
*/
create
table
tb (id
int
, pid
int
,
name
nvarchar(20))
insert
into
tb
values
(1 , 0 ,
'食品'
)
insert
into
tb
values
(2 , 1 ,
'水果'
)
insert
into
tb
values
(3 , 1 ,
'蔬菜'
)
insert
into
tb
values
(4 , 2 ,
'香蕉'
)
insert
into
tb
values
(5 , 2 ,
'苹果'
)
insert
into
tb
values
(6 , 3 ,
'青菜'
)
go
--查询各节点的父路径函数
create
function
f_pid(@id
int
)
returns
varchar
(100)
as
begin
declare
@re_str
as
varchar
(100)
set
@re_str =
''
select
@re_str =
name
from
tb
where
id = @id
while exists (
select
1
from
tb
where
id = @id
and
pid <> 0)
begin
select
@id = b.id , @re_str = b.
name
+
','
+ @re_str
from
tb a , tb b
where
a.id = @id
and
a.pid = b.id
end
return
@re_str
end
go
select
* , dbo.f_pid(id) 路径
from
tb
order
by
id
drop
table
tb
drop
function
f_pid
/*
标题：查询所有节点及其所有子节点的函数
*/
--生成测试数据
create
table
tb(id
varchar
(10),pid
varchar
(10))
insert
into
tb
select
'a'
,
null
insert
into
tb
select
'b'
,
'a'
insert
into
tb
select
'c'
,
'a'
insert
into
tb
select
'd'
,
'b'
insert
into
tb
select
'e'
,
'b'
insert
into
tb
select
'f'
,
'c'
insert
into
tb
select
'g'
,
'c'
go
--创建用户定义函数
create
function
f_getchild(@id
varchar
(10))
returns
varchar
(8000)
as
begin
declare
@i
int
, @ret
varchar
(8000)
declare
@t
table
(id
varchar
(10) , pid
varchar
(10) ,
level
int
)
set
@i = 1
insert
into
@t
select
id , pid , @i
from
tb
where
id = @id
while @@rowcount <> 0
begin
set
@i = @i + 1
insert
into
@t
select
a.id , a.pid , @i
from
tb a , @t b
where
a.pid = b.id
and
b.
level
= @i - 1
end
select
@ret =
isnull
(@ret ,
''
) + id +
','
from
@t
return
left
(@ret , len(@ret) - 1)
end
go
--执行查询
select
id , children =
isnull
(dbo.f_getchild(id) ,
''
)
from
tb
group
by
id
go
--输出结果
/*
id children
---------- -------------
a a,b,c,d,e,f,g
b b,d,e
c c,f,g
d d
e e
f f
g g
（所影响的行数为 7 行）
*/
--删除测试数据
drop
function
f_getchild
drop
table
tb
================================================================
/*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(字符串形式显示)
*/
--生成测试数据
create
table
tb(id
varchar
(3) , pid
varchar
(3) ,
name
varchar
(10))
insert
into
tb
values
(
'001'
,
null
,
'广东省'
)
insert
into
tb
values
(
'002'
,
'001'
,
'广州市'
)
insert
into
tb
values
(
'003'
,
'001'
,
'深圳市'
)
insert
into
tb
values
(
'004'
,
'002'
,
'天河区'
)
insert
into
tb
values
(
'005'
,
'003'
,
'罗湖区'
)
insert
into
tb
values
(
'006'
,
'003'
,
'福田区'
)
insert
into
tb
values
(
'007'
,
'003'
,
'宝安区'
)
insert
into
tb
values
(
'008'
,
'007'
,
'西乡镇'
)
insert
into
tb
values
(
'009'
,
'007'
,
'龙华镇'
)
insert
into
tb
values
(
'010'
,
'007'
,
'松岗镇'
)
go
--创建用户定义函数
create
function
f_cid(@id
varchar
(10))
returns
varchar
(8000)
as
begin
declare
@i
int
, @ret
varchar
(8000)
declare
@t
table
(id
varchar
(10) , pid
varchar
(10) ,
level
int
)
set
@i = 1
insert
into
@t
select
id , pid , @i
from
tb
where
id = @id
while @@rowcount <> 0
begin
set
@i = @i + 1
insert
into
@t
select
a.id , a.pid , @i
from
tb a , @t b
where
a.pid = b.id
and
b.
level
= @i - 1
end
select
@ret =
isnull
(@ret ,
''
) + id +
','
from
@t
return
left
(@ret , len(@ret) - 1)
end
go
--执行查询
select
id , children =
isnull
(dbo.f_cid(id) ,
''
)
from
tb
group
by
id
drop
table
tb
drop
function
f_cid
/*
id children
---- ---------------------------------------
001 001,002,003,004,005,006,007,008,009,010
002 002,004
003 003,005,006,007,008,009,010
004 004
005 005
006 006
007 007,008,009,010
008 008
009 009
010 010
（所影响的行数为 10 行）
*/
/*
标题：SQL SERVER 2000中查询指定节点及其所有父节点的函数(字符串形式显示)
*/
/*
create
table
tb(id
varchar
(3) , pid
varchar
(3) ,
name
varchar
(10))
insert
into
tb
values
(
'001'
,
null
,
'广东省'
)
insert
into
tb
values
(
'002'
,
'001'
,
'广州市'
)
insert
into
tb
values
(
'003'
,
'001'
,
'深圳市'
)
insert
into
tb
values
(
'004'
,
'002'
,
'天河区'
)
insert
into
tb
values
(
'005'
,
'003'
,
'罗湖区'
)
insert
into
tb
values
(
'006'
,
'003'
,
'福田区'
)
insert
into
tb
values
(
'007'
,
'003'
,
'宝安区'
)
insert
into
tb
values
(
'008'
,
'007'
,
'西乡镇'
)
insert
into
tb
values
(
'009'
,
'007'
,
'龙华镇'
)
insert
into
tb
values
(
'010'
,
'007'
,
'松岗镇'
)
go
--查询各节点的父路径函数
create
function
f_pid(@id
varchar
(3))
returns
varchar
(100)
as
begin
declare
@re_str
as
varchar
(100)
set
@re_str =
''
select
@re_str =
name
from
tb
where
id = @id
while exists (
select
1
from
tb
where
id = @id
and
pid
is
not
null
)
begin
select
@id = b.id , @re_str = b.
name
+
','
+ @re_str
from
tb a , tb b
where
a.id = @id
and
a.pid = b.id
end
return
@re_str
end
go
select
* , dbo.f_pid(id) 路径
from
tb
order
by
id
drop
table
tb
drop
function
f_pid
/*
id pid
name
路径
---- ---- ---------- ---------------------------
001
NULL
广东省 广东省
002 001 广州市 广东省,广州市
003 001 深圳市 广东省,深圳市
004 002 天河区 广东省,广州市,天河区
005 003 罗湖区 广东省,深圳市,罗湖区
006 003 福田区 广东省,深圳市,福田区
007 003 宝安区 广东省,深圳市,宝安区
008 007 西乡镇 广东省,深圳市,宝安区,西乡镇
009 007 龙华镇 广东省,深圳市,宝安区,龙华镇
010 007 松岗镇 广东省,深圳市,宝安区,松岗镇
（所影响的行数为 10 行）
*/
==================================================================
id-每个节结点的唯一标识，可以是流水号
Code-本级代码
pid-父节点的id
现在要求写出查询各个结点的select语句！希望各位高手给点建议
code name id code pid name
0
01 服装 1 01 0 服装
0101 男装 2 01 1 男装
010101 西装 3 01 2 西装
01010101 全毛 4 01 3 全毛
01010102 化纤 5 02 3 化纤
010102 休闲装 6 02 2 休闲装
0102 女装 7 02 1 女装
010201 套装 8 01 7 套装
010202 职业装 9 02 7 职业装
010203 休闲装 10 03 7 休闲装
010204 西装 11 04 7 西装
01020401 全毛 12 01 11 全毛
01020402 化纤 13 02 11 化纤
010205 休闲装 14 05 7 休闲装
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-23 02:37:24
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF
NOT
OBJECT_ID(
'[tb]'
)
IS
NULL
DROP
TABLE
[tb]
GO
CREATE
TABLE
[tb]([id]
INT
,[code] NVARCHAR(10),[pid]
INT
,[
name
] NVARCHAR(10))
INSERT
[tb]
SELECT
1,
'01'
,0,N
'服装'
UNION
ALL
SELECT
2,
'01'
,1,N
'男装'
UNION
ALL
SELECT
3,
'01'
,2,N
'西装'
UNION
ALL
SELECT
4,
'01'
,3,N
'全毛'
UNION
ALL
SELECT
5,
'02'
,3,N
'化纤'
UNION
ALL
SELECT
6,
'02'
,2,N
'休闲装'
UNION
ALL
SELECT
7,
'02'
,1,N
'女装'
UNION
ALL
SELECT
8,
'01'
,7,N
'套装'
UNION
ALL
SELECT
9,
'02'
,7,N
'职业装'
UNION
ALL
SELECT
10,
'03'
,7,N
'休闲装'
UNION
ALL
SELECT
11,
'04'
,7,N
'西装'
UNION
ALL
SELECT
12,
'01'
,11,N
'全毛'
UNION
ALL
SELECT
13,
'02'
,11,N
'化纤'
UNION
ALL
SELECT
14,
'05'
,7,N
'休闲装'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;
WITH
T
AS
(
SELECT
CAST
(CODE
AS
VARCHAR
(20))
AS
CODE,*,
CAST
(ROW_NUMBER() OVER(
ORDER
BY
id)
AS
VARBINARY(
MAX
))
AS
px
FROM
tb
AS
A
WHERE
NOT
EXISTS(
SELECT
*
FROM
tb
WHERE
id=A.pid)
UNION
ALL
SELECT
CAST
(B.CODE+A.CODE
AS
VARCHAR
(20)),A.*,
CAST
(B.px+
CAST
(ROW_NUMBER() OVER(PARTITION
BY
B.pid
ORDER
BY
A.id)
AS
BINARY
(4))
AS
VARBINARY(
MAX
))
FROM
tb
AS
A
JOIN
T
AS
B
ON
A.pid=B.id
)
SELECT
Code,
Name
FROM
T
ORDER
BY
px
/*
Code
Name
-------------------- ----------
01 服装
0101 男装
010101 西装
01010101 全毛
01010102 化纤
010102 休闲装
0102 女装
010201 套装
010202 职业装
010203 休闲装
010204 西装
01020401 全毛
01020402 化纤
010205 休闲装
(14 行受影响)
*/
扫一扫
2786
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
