查询中的 2 个 LEFT JOINS 计数
我有一个下载组件，希望类别提前显示子目录和项目计数。两者都有效，但当试图同时获得两者时，结果是两者的乘积。
我使用的代码：
选择一个。*，
count(b.parentid) AS catscount,
count(c.id) AS itemscount
FROM (jos_foc_downl_categories AS a LEFT JOIN jos_foc_downl_items AS c ON c.catid = a.id )
左连接 jos_foc_downl_categories AS b ON b.parentid = a.id
其中 a.parentid=0
按 a.id 分组
这会产生一个包含 4 个子类别和 5 个文件的类别，其中 catscount 的数量为 20，itemscount 的数量为 20。
这有什么问题吗？谢谢！
I have a download-component and want the categories to display the subcats and item count in advance. 1 of both works, but when trying to get both, the result is the multiply of both.
The code I use:
SELECT a.*,
count(b.parentid) AS catscount,
count(c.id) AS itemscount
FROM (jos_foc_downl_categories AS a LEFT JOIN jos_foc_downl_items AS c ON c.catid = a.id )
LEFT JOIN jos_foc_downl_categories AS b ON b.parentid = a.id
WHERE a.parentid=0
GROUP BY a.id
This results for a category with 4 subcategories and 5 files in the number 20 for catscount and 20 for itemscount.
What's wrong with this? Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您正在计算所有行，包括重复值。使用 DISTINCT 仅对每个不同值计数一次：
You're counting all rows, including duplicated values. Use DISTINCT to only count each distinct value once: