mysql 找到根节点_根据子节点获取根节点

CREATE PROCEDURE [dbo].[GetRootID]

(

@nodeID varchar(36)

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @rootID varchar(36), @currentID varchar(36)

SELECT @rootID = ParentOrgID, @currentID = @nodeID FROM Organization WHERE OrgID = @nodeID;

WHILE @rootID !='0'

BEGIN

SELECT @rootID = ParentOrgID, @currentID = OrgID FROM Organization WHERE OrgID = @rootID;

END

RETURN @currentID;

END

GO

因为我的节点与跟节点是用的唯一标识，是varchar 类型，如果是INT 类型的话，将varchar 改为int  并将@rootID !='0' 改为@rootID>0;即可。

查询语句

DECLARE @return_value varchar(36)

EXEC @return_value = [dbo].[GetRootID]

@nodeID = '03941ca4-2ee9-4c9c-84f9-df35cd407809'

select @return_value