SQL中的循环获取根节点

方法一：

DECLARE @employeeid INT;
set @employeeid = 8115;

DECLARE @id INT;
DECLARE @pid int;
DECLARE @level int;
SET @id=-1
--获取父节点id
SELECT @id = RecID, @pid = PID, @level = [Level]
FROM Organization.dbo.OfficePlace
WHERE recid= (SELECT TOP 1 OfficePlaceID FROM Organization.dbo.Employees WHERE EmployeeId=@employeeid)

WHILE (@pid!=-1)
BEGIN
  SELECT @id = RecID,@pid = PID,@level = [Level] 
  FROM Organization.dbo.OfficePlace
  where status=0 AND RecID=@pid
END
PRINT @id

方法二： DECLARE @officePlaceId INT;

DECLARE @officePlaceId INT;
 set @officePlaceId=117;

 DECLARE @id INT;
        SELECT  @id = ISNULL(( SELECT TOP 1
                                        pid
                               FROM     Organization.dbo.OfficePlace
                               WHERE    RecID = @officePlaceId
                                        AND pid <> -1
                                        AND pid IS NOT NULL
                             ), -1) 
        WHILE @@ROWCOUNT > 0
            AND @id > 0
            BEGIN
                SELECT  @id = pid
                FROM    Organization.dbo.OfficePlace
                WHERE   RecID = @id
                        AND pid <> -1
                        AND pid IS NOT NULL
            END
        print @id 

　　

你觉得那种好呢？

 

转载于:https://www.cnblogs.com/stevenjson/p/5007762.html