尝试使用递归公用表表达式(CTE):

Try this using a recursive Common Table Expression (CTE):

SQL提琴

MS SQL Server 2008架构设置:

CREATE TABLE [Folders](
    [FOL_PK] [int] IDENTITY(1,1) NOT NULL,
    [FOL_Name] [varchar](200) NOT NULL,
    [FOL_FOL_FK] [int] NULL,
 CONSTRAINT [PK__Folders__FOL_PK] PRIMARY KEY CLUSTERED 
(
    [FOL_PK] ASC
))

ALTER TABLE [dbo].[Folders]  
  WITH CHECK ADD  CONSTRAINT [FK_Folders_Folders] FOREIGN KEY([FOL_FOL_FK])
REFERENCES [dbo].[Folders] ([FOL_PK])

ALTER TABLE [dbo].[Folders] CHECK CONSTRAINT [FK_Folders_Folders]



INSERT INTO Folders(FOL_Name, FOL_FOL_FK)
VALUES ('Level 1', NULL),
       ('Level 1.1', 1),
       ('Level 1.2', 1),
       ('Level 1.3', 1),
       ('Level 1.2.1', 3),
       ('Level 1.2.2', 3),
       ('Level 1.2.3', 3),
       ('Level 1.2.2.1', 6),
       ('Level 1.2.2.2', 6),
       ('Level 1.2.2.3', 6),
       ('Level 1.3.1', 4),
       ('Level 1.3.2', 4)

查询1 :

DECLARE @FolderId Int = 9

;WITH CTE
AS
(
    SELECT FOL_PK AS PK, FOL_NAME As Name, FOL_FOL_FK AS ParentFK
    FROM Folders
    WHERE FOL_PK = @FolderId
    UNION ALL
    SELECT F.FOL_PK AS PK, F.FOL_NAME AS Name, F.FOL_FOL_FK AS ParentFK
    FROM Folders F
    INNER JOIN CTE C
        ON C.ParentFK = F.FOL_PK

)
SELECT *
FROM CTE

结果 :

Results:

| PK |          Name | ParentFK |
|----|---------------|----------|
|  9 | Level 1.2.2.2 |        6 |
|  6 |   Level 1.2.2 |        3 |
|  3 |     Level 1.2 |        1 |
|  1 |       Level 1 |   (null) |