28,409
社区成员




CREATE TABLE tb(selfid VARCHAR(10),name VARCHAR(10),belogn VARCHAR(10))
INSERT tb SELECT '001', '张一', '0'
UNION ALL SELECT '002' , '张二' , '001'
UNION ALL SELECT '003' , '张三' , '002'
UNION ALL SELECT '004' , '张四' , '001'
GO
CREATE PROC getSon
(@name VARCHAR(10)/* --要查找的姓名*/)
AS
BEGIN
SELECT *,lvl=0 INTO # FROM tb WHERE 1=0
DECLARE @pName VARCHAR(10),@lvl INT,@sel VARCHAR(10),@be VARCHAR(10)
SET @lvl=0
myLoop:
BEGIN
SET @pName=@name
SELECT TOP 1 @lvl=@lvl+1,@name=a.name,@sel=a.selfid,@be=a.belogn FROM tb b
LEFT JOIN tb a
ON a.belogn=b.selfid
WHERE b.name=@pName
END
IF @name IS NOT NULL
BEGIN
INSERT # SELECT @sel,@name,@be,@lvl
GOTO myLoop
END
SELECT * FROM #
DROP TABLE #
END
GO
EXEC getSon '张一'
/*
002 张二 001 1
003 张三 002 2
*/
DROP TABLE tb
DROP PROC getSon
GO