SQL查询:如何使没有孩子的标签成为父母? [英] sql query: how to make the tags without children to become parents?
本文介绍了SQL查询:如何使没有孩子的标签成为父母?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个标签表,
tag_id tag_name parent_id cat_id
3 Tagname-1 NULL NULL
5 tagname-2 NULL NULL
6 tagname-3 NULL NULL
9 tagname-4 NULL NULL
11 tagname-5 3 NULL
13 tagname-6 3 NULL
15 tagname-8 5 NULL
17 tagname-9 5 NULL
18 tagname-10 NULL NULL
20 tagname-11 6 NULL
22 tagname-12 9 NULL
24 tagname-13 NULL NULL
26 tagname-14 NULL NULL
28 tagname-15 NULL NULL
我想返回这样的结果
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
所以在这里我到目前为止提出的查询,
So here the query I came out with so far,
SELECT
a.tag_id as ParentID,
a.tag_name as ParentName,
b.TotalChildren
FROM root_tags a INNER JOIN
(
SELECT parent_id, COUNT(1) as TotalChildren
FROM root_tags
WHERE parent_id <> tag_id
GROUP BY parent_id
) b
ON a.tag_id = b.parent_id
ORDER BY ParentID
但是,不幸的是,它只会返回这样的结果,
But, unfortunately, it only return the result like this,
ParentID ParentName TotalChildren
3 Tagname-1 2
5 tagname-2 2
6 tagname-3 1
9 tagname-4 1
这意味着它失去了没有孩子的父母.
Which means it is missing the parents without any children.
我如何制作没有孩子的标签也可以成为父母?或者换句话说,如何让没有父母的标签自己成为父母?
How can I make the tags without children to become parents too? or in other words, how to make the tags without parents to become parents themselves?
SELECT
a.tag_id as ParentID,
a.tag_name as ParentName,
b.TotalChildren
FROM root_tags a LEFT OUTER JOIN
(
SELECT parent_id, COUNT(1) as TotalChildren
FROM root_tags
WHERE parent_id <> tag_id
GROUP BY parent_id
) b
ON a.tag_id = b.parent_id
ORDER BY ParentID
以上答案返回
ParentID ParentName TotalChildren
3 Tagname-1 2
5 tagname-2 2
6 tagname-3 1
9 tagname-4 1
11 tagname-5 NULL
13 tagname-6 NULL
15 tagname-8 NULL
17 tagname-9 NULL
18 tagname-10 NULL
20 tagname-11 NULL
22 tagname-12 NULL
24 tagname-13 NULL
26 tagname-14 NULL
28 tagname-15 NULL
这是不正确的,因为它会返回所有子项.
which is incorrect as it returns all the children.
推荐答案
首选对子查询的联接:
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
这篇关于SQL查询:如何使没有孩子的标签成为父母?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文