22,206
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb (
id int,
parentID1 int,
parentID2 int );
go
insert into tb
select 1, 2, 3 union all
select 2,4, NULL union all
select 3 ,4,5 union all
select 4 ,5, NULL union all
select 5 ,9,null
go
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from
(select id,parentID1 parentid from tb union select id,parentID2 from tb)a
,@re b
where a.id=b.id and b.level=@l-1 and a.parentid is not null
end
update @re set level=@l-level
return
end
go
SELECT distinct id FROM dbo.f_getP(1) where id<>1
go
/*
(5 行受影响)
id
-----------
2
3
4
5
9
(5 行受影响)
*/
create table tbl
(
id int,
parentID1 int,
parentID2 int
)
go
insert into tbl
select 1, 2, 3 union all select
2,4, NULL union all select
3 ,4,5 union all select
4 ,5, NULL union all select
5 ,9,null
go
IF OBJECT_ID('GetTreeNode','tf') IS NOT NULL
DROP FUNCTION GetTreeNode
GO
CREATE FUNCTION GetTreeNode(@id int)
RETURNS @t2 table(id INT,id2 int,id3 int)
AS
BEGIN
DECLARE @t1 TABLE(id int)
DECLARE @t3 TABLE(id int)
INSERT INTO @t1 SELECT @id
INSERT INTO @t3 SELECT @id
WHILE EXISTS(SELECT 1 FROM tbl WHERE id in (select id from @t1))
BEGIN
INSERT INTO @t2 SELECT id,parentID1,parentID2 FROM tbl WHERE id in (select id from @t1)
IF EXISTS(SELECT 1 FROM tbl WHERE (id IN (SELECT id2 FROM @t2)) or (id IN (SELECT id3 FROM @t2)))
BEGIN
DECLARE @temp TABLE(id int)
INSERT INTO @temp SELECT id FROM tbl WHERE (id not in (select id from @t3)) and (id IN (SELECT id2 FROM @t2) or id IN (SELECT id3 FROM @t2))
DELETE FROM @t1
INSERT INTO @t1 SELECT * FROM @temp
INSERT INTO @t3 SELECT * FROM @temp
DELETE FROM @temp
END
ELSE
BEGIN
RETURN
END
END
RETURN
END
GO
SELECT * FROM dbo.GetTreeNode(4)
go