从自连接表中获取父级的父级的父级

Question

请复制并运行以下脚本。

DECLARE @Locations TABLE
(
    LocationId INT,
    LocationName VARCHAR(50),
    ParentId INT
)

INSERT INTO @Locations SELECT 1, 'Europe', NULL
INSERT INTO @Locations SELECT 2, 'UK', 1
INSERT INTO @Locations SELECT 3, 'England', 2
INSERT INTO @Locations SELECT 4, 'Scotland', 2
INSERT INTO @Locations SELECT 5, 'Wales', 2
INSERT INTO @Locations SELECT 6, 'Cambridgeshire', 3
INSERT INTO @Locations SELECT 7, 'Cambridge', 6
INSERT INTO @Locations SELECT 8, 'North Scotland', 4
INSERT INTO @Locations SELECT 9, 'Inverness', 8
INSERT INTO @Locations SELECT 10, 'Somerset', 3
INSERT INTO @Locations SELECT 11, 'Bath', 10
INSERT INTO @Locations SELECT 12, 'Poland', 1
INSERT INTO @Locations SELECT 13, 'Warsaw', 12

我需要以下结果

谢谢。

Answer 1

对于当前的数据集，您无法做到这一点；您如何知道在 LocationId=11 的情况下，您有一个县/国家/大陆，而在 LocationId=13 的情况下，没有县 - 只是一个国家/大陆？？

您如何知道从输出结果中“跳过”萨默塞特、北苏格兰等条目？

您在这里肯定需要更多信息......

通过此递归 CTE（通用表表达式）查询，您可以将“梯子”从层次结构上升到顶部，对于任何给定位置：

DECLARE @LocID INT = 13

;WITH LocationHierarchy AS
(
    SELECT LocationId, LocationName, ParentId, 1 AS 'Level'
    FROM @Locations
    WHERE LocationId = @LocID

    UNION ALL

    SELECT l.LocationId, l.LocationName, l.ParentId, lh.Level + 1 AS 'Level'
    FROM @Locations l
    INNER JOIN LocationHierarchy lh ON lh.ParentId = l.LocationId
)
SELECT
    LocationName,
    LocationId,
    Level
FROM LocationHierarchy

此 CTE 有效在 SQL Server 2005 及更高版本上 - 在 SQL Server 2000 上，不幸的是（是时候升级了！！）。

这再次允许您在单个条目的层次结构中向上移动 - 但它不可能返回您正在查找的数据集 - 没有足够的信息来根据当前数据确定这一点。

对于 @LocID=13 （华沙），您将得到以下输出：

LocationName    LocationId  Level
  Warsaw               13             1
  Poland               12             2
  Europe                1             3

对于 @LocID=7 （剑桥），您将得到：

LocationName    LocationId  Level
  Cambridge             7             1
  Cambridgeshire        6             2
  England               3             3
  UK                    2             4
  Europe                1             5

从那里开始，您必须在您的应用程序中使用一些智能功能来获得您正在寻找的确切输出。