IF OBJECT_ID(N'TEMPDB..#TB') IS NOT NULL
    DROP TABLE #TB
GO

CREATE TABLE #TB
(
FItemID INT,
FName NVARCHAR(100),
FParentID INT,
FLevel INT
)
GO

INSERT INTO #TB
SELECT 1,'佳天大厦',0,1
UNION ALL
SELECT 2,'六楼',1,2
UNION ALL
SELECT 3,'6205',2,3
UNION ALL
SELECT 4,'六楼',1,2
UNION ALL
SELECT 5,'6207',4,3
UNION ALL
SELECT 6,'童谣大厦',0,1
UNION ALL
SELECT 7,'八楼',6,2
UNION ALL
SELECT 8,'8305',7,3


SELECT T1.FName AS FBuildNo,T2.FName AS FLayer,T3.FName AS FRoom FROM #TB T1 
INNER JOIN #TB T2 ON T1.FItemID = T2.FParentID
INNER JOIN #TB T3 ON T2.FItemID = T3.FParentID

FBuildNo             FLayer               FRoom
-------------------- -------------------- --------------------
佳天大厦                 六楼                   6205
佳天大厦                 六楼                   6207
童谣大厦                 八楼                   8305

(3 行受影响)