基于条件的MySQL计数 [英] MySQL Count based on conditions

问题描述

大家好，我有一个类别结构，我需要根据parent_id来计算类别级别.

例如:

cname cid category_parent_id
猫1 ¦ 1 ¦ 0
猫2 ¦ 2 ¦ 0
猫3 ¦ 3 ¦ 1
猫4 ¦ 4 ¦ 1
猫5 ¦ 5 ¦ 3
猫6 ¦ 6 ¦ 3
猫7 ¦ 7 ¦ 4
猫8 ¦ 8 ¦ 0
猫9 ¦ 9 ¦ 8
猫10 ¦ 10 ¦ 8
猫11 ¦ 11 ¦ 0
猫12 ¦ 12 ¦ 11
猫13 ¦ 13 ¦ 11
猫14 ¦ 14 ¦ 0

我需要计算出cat 6的category_parent_id为3，cat 3的category_parent_id为1，因此这应该计数3次，使其深度达到3级.


我无法弄清楚如何根据类别的父项来计算类别的重复出现次数.

感谢您的任何建议.

Hi There,

Hi all, I have a category structure and I need to count the category levels based on the parent_id.

For example:

cname ¦cid ¦ category_parent_id
cat 1 ¦ 1 ¦ 0
cat 2 ¦ 2 ¦ 0
cat 3 ¦ 3 ¦ 1
cat 4 ¦ 4 ¦ 1
cat 5 ¦ 5 ¦ 3
cat 6 ¦ 6 ¦ 3
cat 7 ¦ 7 ¦ 4
cat 8 ¦ 8 ¦ 0
cat 9 ¦ 9 ¦ 8
cat 10 ¦ 10 ¦ 8
cat 11 ¦ 11 ¦ 0
cat 12 ¦ 12 ¦ 11
cat 13 ¦ 13 ¦ 11
cat 14 ¦ 14 ¦ 0

I need to count cat 6 has a category_parent_id of 3 and cat 3 has a category_parent_id of 1 so this should count 3 times making it 3 levels deep.


I cant work out how to count the repeated occurrences of a category based on it''s parent.

Thanks for any advice.

推荐答案

递归选择在这里是您的朋友；

Recursive selects are your friend here;

with t as
(
	select * from testtbl as t1
	union all
	select t2.* from testtbl as t2
	join t on t.cid=t2.category_parent_id
)
select cname, count(1) as depth from t
group by cname

我没有访问MySql实例的权限，所以我只在SQLServer上对其进行了测试.

希望这会有所帮助，
弗雷德里克

实际上，请忽略所有这些.我认为MySQL不支持它:(

I haven''t got access to a MySql instance here so I''ve only tested that on SQLServer.

Hope this helps,
Fredrik

Actually, ignore of all that. MySQL doens''t support it I think :(

这篇关于基于条件的MySQL计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！