SQL查询: self-referencing foreign key relationship

Question

我有两个表，tabSparePart 和 tabSparePartCategory。每个备件都属于一个备件类别。我需要属于特定类别的所有备件。但问题是，一个备件类别可能是另一个备件类别的“子类别”，它们相互引用(“主类别”在此 FK 列中具有“空”)。

假设我需要 fiSparePartCategory=1 的所有备件以及属于 category=1 的“子类别”类别的所有备件。

如何编写返回所有备件的 SQL 查询，无论有多少级子类别。希望您能理解我的要求。

以下是我所拥有的说明。如何使其动态化，以便无论子类别有多少，它都可以正常工作？

谢谢蒂姆

图片链接:http://www.bilder-hochladen.net/files/4709-lg-jpg.html

编辑:以下是另一种静态方法，该方法在只有一级子类别时有效:

SELECT     SparePartName
FROM         tabSparePart
WHERE     (fiSparePartCategory IN
               (SELECT     idSparePartCategory
                     FROM          tabSparePartCategory
                     WHERE      (idSparePartCategory = 1) OR
                                (fiSparePartCategory = 1)))

Answer 1

您可以使用 recursive Common Table Expression为此。

在您的情况下，您需要获取特定主类别 ID 的所有备件类别 ID，并将其与备件连接。像这样的事情:

WITH SparePartCategories(CategoryId) AS
(
    SELECT c.idSparePartCategory
    FROM tabSparePartCategory c
    WHERE c.idSparePartCategory = 1

    UNION ALL

    SELECT c.idSparePartCategory
    FROM tabSparePartCategory c
    JOIN SparePartCategories parent ON c.fiSparePartCategory = parent.CategoryId
)
SELECT sp.SparePartName
FROM tabSparePart sp
JOIN SparePartCategories spc ON sp.fiSparePartCategory = spc.CategoryId