mysql - 在自连接查询上使用 COUNT

Question

我有一个填充双选列表的查询。该查询通过自联接从同一个表中检索主要类别和子类别。基本上，它使用 id 和父 id 连接自身以获取每个类别中的子类别。我尝试使用 COUNT 来获取子类别的数量，以便我可以将该数字添加到第一个列表的输出中。我没有写查询，我只是想重构它来计算每个主线程中的 subs。

SELECT 
    root.name AS root_name, 
    root.link AS root_link, 
    root.RETAIL AS root_RETAIL, 
    root.WHOLESALE AS root_WHOLESALE,
    root.linkto AS root_linkto, 
    root.keywords AS root_keywords, 
    root.description AS root_description, 
    root.id AS root_id,
    down1.name AS down1_name,
    down1.linkto AS down1_linkto, 
    down1.link AS down1_link, 
    down1.keywords AS down1_keywords,
    down1.description AS down1_description, 
    down1.id AS down1_id, 
    down1.parentid AS down1_parentid, 
    down1.RETAIL AS down1_RETAIL, 
    down1.WHOLESALE AS down1_WHOLESALE
FROM categories AS root
        LEFT OUTER JOIN categories AS down1 ON down1.parentid = root.id
WHERE root.parentid = 0
ORDER BY root_name, down1_name 

这将返回填充两个列表的所有内容。我尝试用 COUNT 将 down1_name 或 down1_id 包围起来，但它计算了所有内容 - 而不是每个主线程中的 sub 数量。我不确定是使用另一个自联接来执行此操作还是使用 COUNT 的嵌套 SELECT。任何见解将不胜感激。谢谢。

Answer 1

尝试使用子查询

SELECT r.name AS r_name, 
       r.link AS r_link, 
       r.RETAIL AS r_RETAIL, 
       r.WHOLESALE AS r_WHOLESALE,
       r.linkto AS r_linkto, 
       r.keywords AS r_keywords, 
       r.description AS r_description, 
       r.id AS r_id,
       d.name AS d_name,
       d.linkto AS d_linkto, 
       d.link AS d_link, 
       d.keywords AS d_keywords,
       d.description AS d_description, 
       d.id AS d_id, 
       d.parentid AS d_parentid, 
       d.RETAIL AS d_RETAIL, 
       d.WHOLESALE AS d_WHOLESALE,
       (SELECT COUNT(*) FROM categories 
         WHERE parentid = r.id) d_count
  FROM categories AS r LEFT OUTER JOIN categories AS d 
    ON d.parentid = r.id
 WHERE r.parentid = 0
 ORDER BY r_name, d_name

SQLFiddle