mysql - (MySQL)MPTT/嵌套集模型上的聚合函数

因此，我使用MySQL并基于嵌套集/修改的预订单树遍历模型将我的类别结构存储在一个名为“Nested_category”的表中，该表包含以下字段：
category_id，name，lft，rgt，published，published
published=1是1或0。。。如果是1，则类别将显示在活动网站上。如果是0，则它不会显示在live网站上，更重要的是，该未发布类别的任何子级也不会显示在live网站上。
我在编写查询以列出具有published=0的所有类别时遇到问题，并且忽略属于具有product_id的类别的子类别的所有类别。
目前我正在使用：

SELECT category_id, lft, rgt FROM nested_category WHERE published = 1

我真的不知道如何让它忽略“子”类别时，家长是“未发表”。
我还试图将此链接到我的“new_products”表，该表包含字段：name、stock、price、category_id、published=1、、，以便我可以编写查询以选择所有具有且属于“已发布”类别的产品。我已经走了这么远：

select @myRight := rgt, @myLeft := lft
from nested_category where name="ELECTRONICS";

select productId, productName, new_products.category_id,
price, stock, new_products.published
from new_products
inner join (
    select category_id, lft, rgt from nested_category
    where published = 1
) cat
on new_products.category_id = cat.category_id
and cat.lft >= @myLeft
and cat.rgt <= @myRight
and new_products.published = 1
order by productName asc

由于上面的查询使用我的第一个查询，所以它不会返回任何“未发布”类别或产品，但是当“已发布”类别是“未发布”类别的后代时，它不会考虑。
希望这有意义！

最佳答案

随着节点深度的增加，性能略有提高：

SELECT node.name, node.category_id, node.lft, node.rgt, (COUNT(parent.name) - 1) AS depth
FROM nested_category as node
LEFT JOIN (
    SELECT nested_category.category_id, nested_category.lft, nested_category.rgt
    FROM nested_category, (
        SELECT category_id, lft, rgt
        FROM nested_category
        WHERE published = 0
    ) notNodeCat
    WHERE nested_category.lft >= notNodeCat.lft
    AND nested_category.rgt <= notNodeCat.rgt ) notNodeCat2
ON notNodeCat2.category_id=node.category_id,
nested_category as parent
LEFT JOIN (
    SELECT nested_category.category_id, nested_category.lft, nested_category.rgt
    FROM nested_category, (
        SELECT category_id, lft, rgt
        FROM nested_category
        WHERE published = 0
    ) notParentCat
    WHERE nested_category.lft >= notParentCat.lft
    AND nested_category.rgt <= notParentCat.rgt ) notParentCat2
ON notParentCat2.category_id=parent.category_id
WHERE notNodeCat2.category_id IS NULL AND notParentCat2.category_id IS NULL AND node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft ASC