SQL查询:如何使父无子 - sql query: how to make the parents without children?

sql query: how to make the parents without children?

本文关键字：何使父查询 SQL | 更新日期: 2023-09-27

我有这个查询来获得我想要的第一个结果，

SELECT parents.tag_id AS ParentID,
       parents.tag_name AS ParentName,
       COUNT(childs.tag_id) AS TotalChildren
FROM root_tags AS parents
    LEFT OUTER JOIN root_tags AS childs
        ON parents.tag_id = childs.parent_id
WHERE parents.parent_id IS NULL
GROUP BY parents.tag_id, parents.tag_name
ORDER BY parents.tag_id

返回,

ParentID    ParentName  TotalChildren
3           Tagname-1   2
5           tagname-2   2
6           tagname-3   1
9           tagname-4   1
18          tagname-10  0
24          tagname-13  0
26          tagname-14  0
28          tagname-15  0

但是我现在有另一个问题，因为我想返回根本没有子节点的父节点

ParentID    ParentName  TotalChildren
18          tagname-10  0
24          tagname-13  0
26          tagname-14  0
28          tagname-15  0

所以我试着查询这个，

SELECT 
    parents.tag_id AS ParentID,
    parents.tag_name AS ParentName,
    COUNT(childs.tag_id) AS TotalChildren
FROM root_tags AS parents
    LEFT OUTER JOIN root_tags AS childs
    ON parents.tag_id = childs.parent_id
WHERE parents.parent_id IS NULL
AND COUNT(childs.tag_id) = '0'
GROUP BY parents.tag_id, parents.tag_name
ORDER BY parents.tag_id

但是它返回错误#1111 - Invalid use of group function

我该如何修复它?

只需在原始查询中添加HAVING子句:

SELECT parents.tag_id AS ParentID,
       parents.tag_name AS ParentName,
       COUNT(childs.tag_id) AS TotalChildren
FROM root_tags AS parents
    LEFT OUTER JOIN root_tags AS childs
        ON parents.tag_id = childs.parent_id
WHERE parents.parent_id IS NULL
GROUP BY parents.tag_id, parents.tag_name
HAVING COUNT(childs.tag_id) = 0
ORDER BY parents.tag_id

你的问题是你试图使用WHERE子句与聚合和功能(COUNT)。但是您应该使用WHERE在聚合之前过滤数据。HAVING用于过滤聚合后的结果

修改这部分

AND COUNT(childs.tag_id) = '0'
GROUP BY parents.tag_id, parents.tag_name

GROUP BY parents.tag_id, parents.tag_name
HAVING COUNT(childs.tag_id) = 0

在GROUP BY后使用HAVING代替WHERE

http://blog.cnizz.com/tag/mysql-error-1111/