代码 名称 级别 父级代码
3520207 XX县 2 35202
352020701 XX镇 3 3520207
35202070110 XX村 4 352020701
3520207011006 XX组 5 35202070110
给一个代码:3520207011006008
查出结果 XX县XX镇XX村XX组
3520207 XX县 2 35202
352020701 XX镇 3 3520207
35202070110 XX村 4 352020701
3520207011006 XX组 5 35202070110
给一个代码:3520207011006008
查出结果 XX县XX镇XX村XX组
/*
标题:查询各节点的父路径函数
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*//*
原始数据及要求结果如下:
--食品
--水果
--香蕉
--苹果
--蔬菜
--青菜
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
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pid
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111' )
insert into BOM values(2,1,'1111_1' )
insert into BOM values(3,2,'1111-1-1' )
insert into BOM values(4,3,'1111-1-1-1')
insert into BOM values(5,1,'1111-2' )go--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
declare @ret varchar(40) while exists(select 1 from BOM where ID=@ID and parentID<>0)
begin
select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
from
BOM a,BOM b
where
a.ID=@ID and b.ID=a.parentID
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go--输出结果
/*
ID parentID
----------- ----------------------------------------
1
2 1
3 1,2
4 1,2,3
5 1
*/--删除测试数据
drop function f_getParent
drop table BOM
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([代码] bigint,[名称] varchar(4),[级别] int,[父级代码] bigint)
insert [TB]
select 3520207,'XX县',2,35202 union all
select 352020701,'XX镇',3,3520207 union all
select 35202070110,'XX村',4,352020701 union all
select 3520207011006,'XX组',5,35202070110DECLARE @i bigint
SET @i = 3520207011006
declare @s varchar(400)
set @s=''
;WITH
DEPTS AS(
SELECT * FROM TB
WHERE [代码] = @i
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM TB A, DEPTS B
WHERE B.[父级代码] = A.[代码]
)
SELECT @s=@s+名称 FROM DEPTS
select @s/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XX组XX村XX镇XX县(1 行受影响)*/drop table TB
insert into tb values('3520207' , 'XX县', 2, '35202')
insert into tb values('352020701' , 'XX镇', 3, '3520207')
insert into tb values('35202070110' , 'XX村', 4, '352020701')
insert into tb values('3520207011006' , 'XX组', 5, '35202070110')
go--查询各节点的父路径函数
create function f_pid(@id varchar(20)) 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 <> '35202')
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
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pid/*
id name 级别 pid 路径
-------------------- ---------- ----------- -------------------- ----------------------------------------------------------------------------------------------------
3520207 XX县 2 35202 XX县
352020701 XX镇 3 3520207 XX县XX镇
35202070110 XX村 4 352020701 XX县XX镇XX村
3520207011006 XX组 5 35202070110 XX县XX镇XX村XX组(所影响的行数为 4 行)*/
Drop table [tb]
Go
Create table [tb]([代码] nvarchar(13),[名称] nvarchar(3),[级别] int,[父级代码] nvarchar(11))
Insert [tb]
Select N'3520207',N'XX县',2,N'35202' union all
Select N'352020701',N'XX镇',3,N'3520207' union all
Select N'35202070110',N'XX村',4,N'352020701' union all
Select N'3520207011006',N'XX组',5,N'35202070110'
Go
--Select * from [tb]-->SQL查询如下:
;with t as
(
select *,name=cast([名称] as varchar(8000)) from tb where charindex([代码],'3520207011006008')>0
union all
select a.*,cast(b.name+a.名称 as varchar(8000))
from tb a,t b where a.[代码]=b.[父级代码]
)
select max(name) from t
/*
-------------------------
XX组XX村XX镇XX县(1 行受影响)
*/
SET @Name = ''SELECT @Name = @Name + [名称] FROM [tables] WHERE '3520207011006008' LIKE [代码] + '%'ORDER BY [级别] ASCSELECT @Name AS Name
Drop table [tb]
Go
Create table [tb]([代码] nvarchar(13),[名称] nvarchar(3),[级别] int,[父级代码] nvarchar(11))
Insert [tb]
Select N'3520207',N'XX县',2,N'35202' union all
Select N'352020701',N'XX镇',3,N'3520207' union all
Select N'35202070110',N'XX村',4,N'352020701' union all
Select N'3520207011006',N'XX组',5,N'35202070110'
Go
--Select * from [tb]-->SQL查询如下:
;with t as
(
select *,name=cast([名称] as varchar(8000)) from tb where [代码]='3520207011006'
union all
select a.*,cast(a.名称+b.name as varchar(8000))
from tb a,t b where a.[代码]=b.[父级代码]
)
select * from t
/*
-------------------------
XX县XX镇XX村XX组(1 行受影响)
*/modify
create table test(代码 varchar(20),名称 varchar(20),级别 int,父级代码 varchar(20))
insert test
select '3520207','XX县',2,'35202' union all
select '352020701','XX镇',2,'3520207' union all
select '35202070110','XX村',2,'352020701' union all
select '3520207011006','XX组',2,'35202070110'
go
;with t as
(
select *,name=cast([名称] as varchar(8000)) from test where charindex([代码],'3520207011006008')>0
union all
select a.*,cast(b.name+a.名称 as varchar(8000))
from test a,t b where a.[代码]=b.[父级代码]
)
select max(name) from t /*
-------------------------
XX组XX村XX镇XX县(1 行受影响)
*/