sql查询：如何使用标记表过滤父标签和子标签？

Question

从此 post - 我必须进一步过滤行，即我必须确保标签必须标记到页面。

这是我的 root_mm_tagged_pa​​ges 表，

tag id  pg_id 
3       11  
5       11  
6       11  
18      12  
24      13  
26      13
3       14

因此我想返回此结果，

ParentID    ParentName  TotalChildren   TotalTagged
3           Tagname-1   2               2
5           tagname-2   2               1
6           tagname-3   1               1
18          tagname-10  0               1
24          tagname-13  0               1
26          tagname-14  0               1

我尝试使用此查询，但收到错误 - #1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 5 行的 'COUNT( tagged.pg_id ) AS TotalTagged FROM root_tags ASparents LEFT OUTER JOI' 附近使用的正确语法，

SELECT 
    parents.tag_id AS ParentID,
    parents.tag_name AS ParentName,
    COUNT(childs.tag_id) AS TotalChildren
    COUNT( tagged.pg_id ) AS TotalTagged

FROM root_tags AS parents
    LEFT OUTER JOIN root_tags AS childs
    ON parents.tag_id = childs.parent_id

    LEFT OUTER JOIN root_mm_tagged_pages AS tagged
    ON tagged.tag_id = parents.tag_id

WHERE parents.parent_id IS NULL
GROUP BY parents.tag_id, parents.tag_name
ORDER BY parents.tag_id

如何修复它并过滤进一步结果？

Answer 1

TotalChildren 别名后缺少逗号。

SELECT 
    parents.tag_id AS ParentID,
    parents.tag_name AS ParentName,
    COUNT(childs.tag_id) AS TotalChildren , --You missed the comma here
    COUNT( tagged.pg_id ) AS TotalTagged
FROM root_tags AS parents
    LEFT OUTER JOIN root_tags AS childs
    ON parents.tag_id = childs.parent_id

    LEFT OUTER JOIN root_mm_tagged_pages AS tagged
    ON tagged.tag_id = parents.tag_id

WHERE parents.parent_id IS NULL
GROUP BY parents.tag_id, parents.tag_name
ORDER BY parents.tag_id