求助:数据的树型查找方法

[NetVan]

数据结构是这样的

selfid 编号
name 姓名
belong 上级编号(0为顶级)


如何查找,下面的一个人! 要求很简单,提供姓名后,找出这个人 但必须这个人是他的下级

给一个思路,谢谢!

[weifengxian2]

select b.* from T a,T b where b.belong=a.selfid and a.name='NAME';
是不是这个意思

[-狙击手-]

只要有一个？ 没多个下属呀

select b.*
from T a
join T b
on b.belong=a.selfid and a.name= 'NAME '

[NetVan]

belogn=selfid 这样只能判断,第一层下级,如果第二层就不行了

[-狙击手-]

我就说没这么简单啦，找到所有的下属吧，直接和间接的

CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GO

CREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID INT
    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
    BEGIN
        SET @ID=@PID
        SELECT @PID=PID FROM BOM WHERE ID=@ID
    END
    RETURN @PID
END
GO

SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO

/*
PID         ID
----------- ----------- 
801         101
801         102
801         103
801         601
801         101
801         105
801         501
801         106
801         121
*/


DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO

[中国风]

关于递归的列子:
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx

[pt1314917]

create table tdd(selfid varchar(10),name varchar(10),belong varchar(10))
insert into tdd select '0001','wsp','0'
insert into tdd select '0002','pp','0001'
insert into tdd select '0003','ww','0002'
insert into tdd select '0004','ss','0003'

create proc ssss
@name varchar(10)
as
select a.name from tdd a,tdd b where a.belong=b.selfid and b.name=@name

exec ssss 'pp'