查找具有关系表的所有父级和子级

Question

我继承了两个表：

表1:Loc

LocID    Field1    Field2
---------------------------
 1       AAA       BBB
 2       CCC       DDD
 3       EEE       FFF
 4       GGG       HHH
 5       III       JJJ
 6       KKK       LLL
 7       MMM       NNN
 8       OOO       PPP
 9       QQQ       RRR
10       SSS       TTT
11       UUU       VVV

表2:LocRel

LocID_A     LocID_B
-----------------------
   1           2
   1           3
   2           4
   2           5
   3           6
   3           7
   5          11
   8           9
   8          10

因此，LocRel表基本上用于指定Loc表中各种记录之间的关系。

我正在努力编写一个sql语句，它将列出所有的关系，如下所示：

LocID   Level   LocIDH_Ancestry    Field1    Field2
---------------------------------------------------
  1       1        NULL            AAA       BBB
  2       2        1               CCC       DDD
  3       2        1               EEE       FFF
  4       3        1,2             GGG       HHH
  5       3        1,2             III       JJJ
  6       3        1,3             KKK       LLL
  7       3        1,3             MMM       NNN
  8       1        NULL            OOO       PPP
  9       2        8               QQQ       RRR
 10       2        8               SSS       TTT
 11       4        1,2,5           UUU       VVV

我一点都不擅长涉及关系的查询，我真的很感激能为我提供一些帮助，帮助我如何实现上述目标。

谢谢大家！

Answer 1

这个答案可能是SQL Server特有的。我不是SQL方面的专家，所以我不知道其中有多少是标准的和/或在其他dbms中采用的。

您可以使用递归查询来实现您提到的那种查询。这是一个很好的起点。

对于您的特定查询，应该使用类似的方法。

WITH MyCTE AS
(
   SELECT
       LocID, 1 as Level,
       NULL as LocIDH_Ancestry,
       Field1, Field2
   FROM
       Table1
   WHERE
       LocID NOT IN (SELECT LocID_B FROM Table2)
   UNION ALL
   SELECT
       t1.LocID,
       c.Level + 1 as Level,
       t2.LocID_A as LocIDH_Ancestry,
       t1.Field1, t1.Field2
   FROM
       Table1 t1
   INNER JOIN
       Table2 t2 ON t1.LocID = t2.LocID_A
   INNER JOIN
       MyCTE c ON t2.LocID_B = c.LocID
)
SELECT *
FROM MyCTE

希望能有所帮助。