您可以使用 CTE 递归构建包含所有父子关系的表，并使用 where 子句仅获取您需要的子树(在我的示例中), 一切都在 CategoryId 下 5) :

You can use a CTE to recursively build a table containing all the parent-child relationships and use the where clause to get only the subtree you need (in my example, everyting under CategoryId 5) :

WITH CategorySubTree AS (
    SELECT cc.CategoryId as SubTreeRoot,
            cc.CategoryId 
            FROM CustomerCategory cc
UNION ALL
    SELECT cst.SubTreeRoot, cc.CategoryId
        FROM CustomerCategory cc
        INNER JOIN CategorySubTree cst ON cst.CategoryId = cc.parentId
)
SELECT cst.CategoryId
FROM CategorySubTree cst
WHERE cst.SubTreeRoot = 5

您可以修改此查询以添加您需要的任何内容，例如，将客户链接到子树中的类别节点:

You can modify this query to add whatever you need, for example, to get customers linked to the category nodes in the subtree :

WITH CategorySubTree AS (
    SELECT cc.CategoryId as SubTreeRoot,
            cc.CategoryId 
            FROM CustomerCategory cc
UNION ALL
    SELECT cst.SubTreeRoot, cc.CategoryId
        FROM CustomerCategory cc
        INNER JOIN CategorySubTree cst ON cst.CategoryId = cc.parentId
)
SELECT cst.CategoryId,cic.CustomerId
FROM CategorySubTree cst
        INNER JOIN CustomerInCategory cic ON cic.CategoryId = cst.CategoryId
WHERE cst.SubTreeRoot = 5

当然，您可以加入更多表格以获取标签和其他所需信息.

And of course you can join further tables to get labels and other needed information.