8.2.4 查找指定节点的所有父节点的示例函数.sqlCREATE FUNCTION f_Pid(@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.PID,@Level FROM tb a,@t_Level b WHERE a.ID=b.ID AND b.Level=@Level-1 END RETURN END GO --上面的用户定义函数可以处理一个节点有多个父节点的情况,对于标准的树形数据而言,由于每个节点仅有一个父节点,所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。 CREATE FUNCTION f_Pid(@ID char(3)) RETURNS @t_Level TABLE(ID char(3)) AS BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL WHILE @@ROWCOUNT>0 BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL END RETURN END
create table tb(id int,pid int) insert into tb select 1 ,0 insert into tb select 2 ,1 insert into tb select 3 ,1 insert into tb select 4 ,2 insert into tb select 5 ,2 insert into tb select 6 ,5 insert into tb select 7 ,6 goCREATE FUNCTION f_Pid(@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.PID,@Level FROM tb a,@t_Level b WHERE a.ID=b.ID AND b.Level=@Level-1 END RETURN END GOselect * from f_pid(6)godrop table tb drop function f_pid/* ID Level ---- ----------- 6 1 5 2 2 3 1 4 0 5(所影响的行数为 5 行)*/
8.2.4 查找指定节点的所有父节点的示例函数.sqlCREATE FUNCTION f_Pid(@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.PID,@Level FROM tb a,@t_Level b WHERE a.ID=b.ID AND b.Level=@Level-1 END RETURN END GO --上面的用户定义函数可以处理一个节点有多个父节点的情况,对于标准的树形数据而言,由于每个节点仅有一个父节点,所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。 CREATE FUNCTION f_Pid(@ID char(3)) RETURNS @t_Level TABLE(ID char(3)) AS BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL WHILE @@ROWCOUNT>0 BEGIN INSERT @t_Level SELECT @ID SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL END RETURN END 经典
create table T(ID int, PID int) insert T select 1, 0 insert T select 2, 1 insert T select 3, 1 insert T select 4, 2 insert T select 5, 2 insert T select 6, 5 insert T select 7, 6 godeclare @T table(ID int,PID int) insert @T select * from T where ID=6while exists(select 1 from T a where exists(select 1 from @T where PID=a.ID) and not exists(select 1 from @T where ID=a.ID)) insert @T select * from T a where exists(select 1 from @T where PID=a.ID) and not exists(select 1 from @T where ID=a.ID)select * from @T order by ID (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)ID PID ----------- ----------- 1 0 2 1 5 2 6 5(所影响的行数为 4 行)
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.PID,@Level
FROM tb a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--上面的用户定义函数可以处理一个节点有多个父节点的情况,对于标准的树形数据而言,由于每个节点仅有一个父节点,所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
create table tb(id int,pid int)
insert into tb select 1 ,0
insert into tb select 2 ,1
insert into tb select 3 ,1
insert into tb select 4 ,2
insert into tb select 5 ,2
insert into tb select 6 ,5
insert into tb select 7 ,6 goCREATE FUNCTION f_Pid(@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.PID,@Level
FROM tb a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GOselect * from f_pid(6)godrop table tb
drop function f_pid/*
ID Level
---- -----------
6 1
5 2
2 3
1 4
0 5(所影响的行数为 5 行)*/
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.PID,@Level
FROM tb a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--上面的用户定义函数可以处理一个节点有多个父节点的情况,对于标准的树形数据而言,由于每个节点仅有一个父节点,所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
经典
insert T select 1, 0
insert T select 2, 1
insert T select 3, 1
insert T select 4, 2
insert T select 5, 2
insert T select 6, 5
insert T select 7, 6 godeclare @T table(ID int,PID int)
insert @T select * from T where ID=6while exists(select 1 from T a where exists(select 1 from @T where PID=a.ID) and not exists(select 1 from @T where ID=a.ID))
insert @T select * from T a where exists(select 1 from @T where PID=a.ID) and not exists(select 1 from @T where ID=a.ID)select * from @T order by ID
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)ID PID
----------- -----------
1 0
2 1
5 2
6 5(所影响的行数为 4 行)