看联机帮助吧。
下面的 Transact-SQL 过程将一个编码的层次展开到任意深度。尽管 Transact-SQL 支持递归,但是使用临时表作为堆栈来跟踪所有正在处理中的项目(已经开始但尚未结束),将更加有效。某个项目一旦处理完毕,将被从堆栈中删除。当发现新的项目时,这些项目将被添加到堆栈中。CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
下面的 Transact-SQL 过程将一个编码的层次展开到任意深度。尽管 Transact-SQL 支持递归,但是使用临时表作为堆栈来跟踪所有正在处理中的项目(已经开始但尚未结束),将更加有效。某个项目一旦处理完毕,将被从堆栈中删除。当发现新的项目时,这些项目将被添加到堆栈中。CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
create table 原表(子节点 varchar(10),父节点 varchar(10),地址 varchar(10))
insert 原表 select 'A001','P001','广州'
union all select 'A002','A001','青岛'
union all select 'A003','A002','广州'
union all select 'A004','A003','广州'
union all select 'A005','A004','上海'
union all select 'A006','A005','广州'
union all select 'A007','P001','广州'
union all select 'A008','P001','杭州'
union all select 'A009','A008','广州'
union all select 'A010','A009','广州'
go--查询的函数
create function f_qry(
@地址 nvarchar(10)
)returns @re table(子节点 nvarchar(10),父节点 nvarchar(10),排序后层 int,地址 nvarchar(10))
as
begin
declare @l int,@p_地址 nvarchar(10)
declare @t table(id nvarchar(10),sid nvarchar(4000),level int) set @l=0
insert @t select 子节点,子节点,@l
from 原表 where 父节点='P001'
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.子节点,b.sid+','+a.子节点,@l
from 原表 a,@t b
where a.父节点=b.id and b.level=@l-1
end
insert @re select a.子节点,a.父节点,0,a.地址
from 原表 a,@t b
where a.子节点=b.id
order by b.sid update @re set @l=case
when 父节点='P001' then 1
when @p_地址=@地址 and 地址=@地址 then @l+1
else @l end
,@p_地址=case
when 父节点='P001' then 地址
else @地址 end
,排序后层=@l
return
end
go--调用函数实现查询
select * from f_qry(N'广州')
go--删除测试
drop table 原表
drop function f_qry/*--测试结果
子节点 父节点 排序后层 地址
---------- ---------- ----------- ----------
A001 P001 1 广州
A002 A001 1 青岛
A003 A002 2 广州
A004 A003 3 广州
A005 A004 3 上海
A006 A005 4 广州
A007 P001 1 广州
A008 P001 1 杭州
A009 A008 1 广州
A010 A009 2 广州(所影响的行数为 10 行)
--*/