从MySQL中的两个连接表中获取多个计数
问题描述:
因此,我有两个表,类别和设计。我想构建一个查询,它将获取所有类别以及任何子类别的数量(categories.parent_id等于categories.id)和任何设计的数量(design.category_id等于categories.id)从MySQL中的两个连接表中获取多个计数
如果我尝试获取这些计数中的一个,一切正常,但是当我尝试使用以下代码时,两者的计数都是相同的数字(而不是正确的数字)。
$this->db->select('categories.id AS id, categories.parent_id AS parent_id, categories.title AS title,
categories.description AS description, categories.img_path AS img_path, COUNT(designs.id) AS design_count,
COUNT(sub_categories.id) as sub_category_count');
$this->db->from('categories');
$this->db->join('designs', 'categories.id = designs.category_id', 'left');
$this->db->join('categories as sub_categories', 'categories.id = sub_categories.parent_id', 'left');
$this->db->group_by('categories.id');
任何帮助将不胜感激,欢呼!
答
假设根类不包含设计,这里是返回必要的信息查询:
SELECT category.id, category.title, subcategory.id, designs.id
FROM categories category
LEFT JOIN categories subcategory ON category.id = subcategory.parent_id
LEFT JOIN designs ON subcategory.id = designs.category_id
WHERE category.parent_id IS NULL
现在,所有你需要做的是应用分组:
SELECT category.id, category.title, COUNT(DISTINCT subcategory.id), COUNT(designs.id)
FROM categories category
LEFT JOIN categories subcategory ON category.id = subcategory.parent_id
LEFT JOIN designs ON subcategory.id = designs.category_id
WHERE category.parent_id IS NULL
GROUP BY category.id, category.title
这里的关键是使用COUNT(DISTINCT ...)
。
+0
DISTINCT为我做了 - 谢谢! – Sam 2012-02-02 13:44:41
答
SELECT c.id,c.title,
IFNULL(sc.counted,0) AS subcategories,
IFNULL(d.counted,0) AS designs
FROM categories c
LEFT JOIN
(SELECT parent_id,COUNT(*) AS counted
FROM categories GROUP BY parent_id) sc
ON c.id=sc.parent_id
LEFT JOIN
(SELECT category_id,COUNT(*) AS counted
FROM designs GROUP BY category_id) d
ON c.id=d.category_id
WHERE c.parent_id IS NULL ;
应该为您提供所需的数字作为原始SQL。
首先创建SQL查询并使用PHPMyadmin等工具执行它。只要它在那里不起作用,它就不能用于任何数据库抽象(并且你还没有告诉你正在使用哪一个,它可能是有限的,并且无论如何你都需要执行一个原始的SQL查询)。 – hakre 2012-02-02 09:28:03
请提供表结构 – 2012-02-02 09:36:37
类别为: id,parent_id,title。 设计为: id,category_id,title。 干杯! – Sam 2012-02-02 09:40:39