mysql - 在此祖先链查询中接受 id 列表而不是一个

Question

我从这里改编了这个。

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := category_id_parent FROM category WHERE id = _id) AS category_id_parent,
        @l := @l + 1 AS lvl
    FROM
         (SELECT @r := (SELECT c.id FROM category c WHERE c.name="Category D"), @l := 0) vars,
        category h
    WHERE @r <> -1) T1
JOIN category T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

我将如何进一步调整它以接受一个 id 列表而不仅仅是一个？

所以而不是SELECT c.id FROM category c WHERE c.name="Category D"

它将是SELECT bc.category_id FROM businesscategory bc WHERE bc.business_id=55（它返回一个 id 列表，而不仅仅是一个）

当前查询示例：

我有一张桌子category

id    |    name    | category_id_parent
-1         N/A              -1
1       Category A          -1
2       Category B          -1
3       Category C           1
4       Category D           3

此查询将返回

id    |   name
1       Category A
3       Category C
4       Category D

由于类别 D 的父级是 C，其父级是 A。

标签： mysql