在SQL Server中创建数据层次结构

Question

因此，我的任务是创建一个SQL Server存储过程来组装员工的地理层次结构。

系统有3种地理类型:

1. 国家地理(根)
2. 区域地理(一级)
3. 区域地理(二级)

数据库中的地理表如下所示:

GeographyID | GeographyType | GeographyName | ParentGeographyID |
-----------------------------------------------------------------
1             National        Nation          NULL
2             Region          South           1
3             Territory       Florida         2

还有一个雇员表。单个员工可以被分配到上述任何地区。

例如，如果员工105被分配到"South"区域，则XREF表中有如下条目:

EmployeeID | GeographyID
------------------------
105           2

我需要做的是给定一个员工ID，构建他们的地理层次结构。因此，员工105的结果看起来像这样:

EmployeeID | TerritoryGeographyID | RegionGeographyID | NationalGeographyID
---------------------------------------------------------------------------
105          NULL                    2                    1

我不知道如何建立这样的数据结构。我希望有人能对这个问题有一些见解。

Answer 1

考虑到预期结果应该显示所有级别，并且级别很少，存在不使用递归CTE的解决方案，例如

WITH G AS (
  SELECT g.GeographyID 
       , t.GeographyID TerritoryGeographyID
       , r.GeographyID RegionGeographyID
       , n.GeographyID NationalGeographyID
  FROM   Geography g
         LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
                              AND (T.GeographyType = 'Territory')
         LEFT JOIN Geography R ON ((g.GeographyID = R.GeographyID) 
                                OR (R.GeographyID = T.ParentGeographyID))
                              AND (R.GeographyType = 'Region')
         LEFT JOIN Geography N ON ((g.GeographyID = N.GeographyID) 
                                OR (N.GeographyID = R.ParentGeographyID))
                              AND (N.GeographyType = 'National')
)
SELECT E.EmployeeID
     , TerritoryGeographyID
     , RegionGeographyID
     , NationalGeographyID
FROM   Employee E
       INNER JOIN G ON E.GeographyID = G.GeographyID;

在CTE中，将Geography从垂直到水平进行分区重组，主要查询JOIN将' PIVOT ed'数据改为员工数据，将[trn].GeographyID更改为[trn].GeographyName可将ID更改为位置名称

分区可以显式地为每个级别定义一个CTE

With N AS (
  SELECT GeographyID, GeographyName
  FROM   Geography
  WHERE  GeographyType = 'National'
), R AS (
  SELECT GeographyID, GeographyName, ParentGeographyID
  FROM   Geography
  WHERE  GeographyType = 'Region'
), T AS (
  SELECT GeographyID, GeographyName, ParentGeographyID
  FROM   Geography
  WHERE  GeographyType = 'Territory'
), G AS (
  SELECT g.GeographyID 
       , t.GeographyID TerritoryGeographyID
       , r.GeographyID RegionGeographyID
       , n.GeographyID NationalGeographyID
  FROM   Geography g
         LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
         LEFT JOIN Geography R ON (g.GeographyID = R.GeographyID)
                               OR (R.GeographyID = T.ParentGeographyID)
         LEFT JOIN Geography N ON (g.GeographyID = N.GeographyID) 
                               OR (N.GeographyID = R.ParentGeographyID)
)
SELECT E.EmployeeID
     , TerritoryGeographyID
     , RegionGeographyID
     , NationalGeographyID
FROM   Employee E
       INNER JOIN G ON E.GeographyID = G.GeographyID;

<<p> SQLFiddle演示/kbd>