mysql - 获取父节点和子节点以及计数

Question

我正在根据 guide from this article 使用嵌套模型.

The nested category table is as follows.

The product table is as follows.

我想获取所选级别的父节点和子节点。例如:如果选择了电视，则应显示查询。此外，还应显示每个类别中每个产品的数量。

Electronics
  Television
    Tube     (2)
    LCD      (1)
    Plasma   (2)

我编写了以下查询

SELECT parent.name, count(product.product_id)
FROM nested_category AS node,nested_category AS parent,nested_category AS midpoint
LEFT JOIN product ON product.product_id=midpoint.category_id
WHERE (node.lft BETWEEN parent.lft AND parent.rgt) AND (node.lft BETWEEN midpoint.lft AND midpoint.rgt) AND midpoint.name='TELEVISIONS'
GROUP BY parent.name
ORDER BY node.lft

我得到的结果在这里

这正确地获取了所有子节点和父节点，但计数错误。 Check the SQL Fiddle here

Answer 1

试试这个:

SELECT parent.category_id as pid, parent.name as pname, SUM(product.product_id IS NOT NULL)
FROM
nested_category AS parent,
nested_category AS midpoint,
nested_category AS node
LEFT JOIN product ON product.category_id=node.category_id
WHERE (node.lft BETWEEN parent.lft AND parent.rgt)
AND (node.lft BETWEEN midpoint.lft AND midpoint.rgt)
AND midpoint.name='TELEVISIONS'
GROUP BY parent.category_id
ORDER BY parent.category_id;