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

Question

因此，我使用 MySQL 并将基于嵌套集/修改后的预序树遍历模型的类别结构存储在表名“nested_category”中，该表具有以下字段:category_id，name，lft，rgt，published

published 为 1 或 0...如果为 1，则该类别将显示在实时站点上。如果它为 0，则它不会显示在实时网站上，更重要的是，该未发布类别的任何子项也不会显示在实时网站上。

我在编写查询以列出具有 published=1 的所有类别时遇到问题，并忽略属于具有 published=0 的类别的所有后代类别>.

目前我正在使用:

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

我真的不知道如何让它在父级“未发布”时忽略“子级”类别。

我还尝试将此链接到我的“new_products”表，该表包含以下字段:product_id、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

由于上述查询使用了我的第一个查询，它不会返回任何“未发布”类别或产品，但它不会考虑“已发布”类别是“未发布”类别的后代。希望这是有道理的!

Answer 1

随着节点深度的增加一点点:

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