按具有产品计数的子节点进行筛选

Question

以下SQL代码工作正常，但我无法选择/筛选类别。带来所有类别。

例如，

Cars (20) (root)
---> Blue cars (12)
----------> Ford (2)
----------> Bmw (9)
----------> Dodge (1)
---> Red cars (9)
----------> Xxxx (9)
---> Black cars (1)
----------> Yy (1)

如何仅选择蓝色汽车的类别。（带子节点）

我该如何更改sql代码才能做到这一点？

我希望结果如下。（如果我选择蓝色汽车类别）

---> blue cars (12)
----------> ford (2)
----------> bmw (9)
----------> dodge (1)

这是sql代码。。。

SELECT 
parent.cid,
parent.title,
(SELECT COUNT(parent2.cid) FROM categories AS parent2 WHERE parent.lft > parent2.lft AND parent.rgt < parent2.rgt) AS depth,
COUNT(products.cat_id) AS total
FROM categories AS node,
categories AS parent,
products
WHERE 
node.lft BETWEEN parent.lft AND parent.rgt 
AND node.cid = products.cat_id
GROUP BY parent.cid having depth <= 5
ORDER BY parent.lft

Answer 1

我想你想得太多了——获取子树是对嵌套集最简单的操作

SELECT *
FROM categories
WHERE lft BETWEEN bluecarleftvalue AND bluecarrightvalue
ORDER BY lft ASC