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 GOCREATE 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 GOSELECT 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
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
GOCREATE 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
GOSELECT 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