34,575
社区成员
发帖
与我相关
我的任务
分享
create table tb (员工标号 varchar(4),上级主管编号 varchar(4))
insert into tb
select '0022','0011' union all
select '0033','0022' union all
select '0044','0011' union all
select '0055','0011' union all
select '0011','0001' union all
select '0009','0002'
go
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@员工标号 varchar(4)) RETURNS @t_Level TABLE(员工标号 varchar(4),上级主管编号 varchar(4))
AS
BEGIN
DECLARE @上级主管编号 varchar(4)
SET @上级主管编号=1
INSERT @t_Level SELECT @员工标号,@上级主管编号
WHILE @@ROWCOUNT>0
BEGIN
SET @上级主管编号=@上级主管编号+1
INSERT @t_Level SELECT a.员工标号,@上级主管编号
FROM tb a,@t_Level b
WHERE a.上级主管编号=b.员工标号
AND b.上级主管编号=@上级主管编号-1
END
RETURN
END
GO
--调用函数查询员工标号 = 0001及其所有子节点
SELECT a.* FROM tb a,f_Cid('0001') b WHERE a.员工标号=b.员工标号 order by a.员工标号
drop table tb
drop function f_cid
/*
员工标号 上级主管编号
---- ------
0011 0001
0022 0011
0033 0022
0044 0011
0055 0011
(所影响的行数为 5 行)
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (emp_id varchar(4),parent varchar(4))
insert into #T
select '0022','0011' union all
select '0033','0022' union all
select '0044','0011' union all
select '0055','0011' union all
select '0011','0001' union all
select '0009','0002'
declare @parent varchar(4)
set @parent ='0001'
--> SQL Server 2000
declare @id int
set @id=1
if object_id('tempdb.dbo.#') is not null drop table #
select id=@id,* into # from #T where parent=@parent
while @@rowcount>0
begin
set @id=@id+1
insert # select @id,a.* from #T a join # b on a.parent=b.emp_id where b.id=@id-1
end
select emp_id from # order by 1
/*
emp_id
------
0011
0022
0033
0044
0055
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (emp_id varchar(4),parent varchar(4))
insert into #T
select '0022','0011' union all
select '0033','0022' union all
select '0044','0011' union all
select '0055','0011' union all
select '0011','0001' union all
select '0009','0002'
declare @parent varchar(4)
set @parent ='0001'
;
with T as
(
select * from #T where parent = @parent
union all
select a.* from #T a join T b on a.parent=b.emp_id
)
select emp_id from T order by 1
/*
emp_id
------
0011
0022
0033
0044
0055
*/
--测试数据
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_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 招远市
--*/