我有一个适用于我的网站的嵌套集模型，其中包含子类别等中的项目。除了一个我无法解决的问题之外，它运行得很好。
+---------+-----------------------------+
| item_id | item_name |
+---------+-----------------------------+
| 1 | Laptop |
| 2 | iPod Classic 80GB |
| 3 | iPod Classic 160GB |
+---------+-----------------------------+
+---------+-------------+
| item_id | category_id |
+---------+-------------+
| 1 | 4 |
| 2 | 2 |
| 3 | 2 |
+---------+-------------+
+-------------+--------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+--------------------+-----+-----+
| 1 | iPod | 1 | 6 |
| 2 | Classic | 2 | 3 |
| 3 | Nano | 4 | 5 |
| 4 | Computers | 7 | 8 |
+-------------+--------------------+-----+-----+
使用以下查询：
SELECT parent.name, COUNT(product.item_id)
FROM Category AS node, Category AS parent, Item_Category AS product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
给出以下输出：
+-----------------+------------------------+
| name | COUNT(product.item_id) |
+-----------------+------------------------+
| iPod | 2 |
| Classic | 2 |
| Computers | 1 |
+-----------------+------------------------+
换句话说，所有没有产品的字段都不会显示。现在解决问题，我想向他们展示 COUNT() 结果 = 0。我的查询如何实现这一点？ :)
对我来说听起来像是 LEFT OUTER JOIN 的任务，如下所示：
SELECT parent.name, COUNT(product.item_id),
(select count(*) from Category parent2
where parent.lft > parent2.lft
and parent.rgt < parent2.rgt) as depth
FROM Category parent
LEFT OUTER JOIN Category node
ON node.lft BETWEEN parent.lft AND parent.rgt
LEFT OUTER JOIN Item_Category product
ON node.category_id = product.category_id
GROUP BY parent.name
ORDER by node.lft
因此，您可以确保显示所有类别。请注意，我不是 100% 确定。
编辑：添加了深度子选择，尝试一下。
编辑：删除逗号
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
