问个sql语句有关于count()，如果有人发过了，请给个连接

ponych

表 category 有三个字段
`id`,`title`,`parentID`

测试数据如下
1,玩,0
2,足球,1
3,篮球,1
4,学,0
5,语文,4
6,音乐,4
7,数学,4
8,睡,0

目的
id title child_count
1 玩     2
2 学    3
8 睡    0
希望能用一句sql完成

[ 本帖最后由 ponych 于 2008-2-28 11:16 编辑 ]

camham

select c1.id,c1.title,count(c2.cid) from c c1,c c2 where c1.id*=c2.id
and c1.parentd=0 group by c1.id

ponych

自己研究一夜
select t1.id,t1.title,count(t2.id) as `count`
from category as t1 left join category as t2
on t1.parentID='0'
AND t2.parentID = t1.id
group by t1.id

谢谢精灵王。我试试你的sql，晚点再回来

yueliangdao0608

mysql> set names gbk; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +----+-------+-----+ | id | c_str | num | +----+-------+-----+ |  1 | 玩      |   0 | |  2 | 足球      |   1 | |  3 | 篮球      |   1 | |  4 | 学     |   0 | |  5 | 语文      |   4 | |  6 | 音乐      |   4 | |  7 | 数学     |   4 | |  8 | 睡     |   0 | +----+-------+-----+ 8 rows in set (0.00 sec) mysql> select a.id,a.c_str,sum(case when b.num is null then 0 else 1 end)as child_count from (select * from t where num = 0) a left join  t b on a.id = b.num group by a.id; +----+-------+-------------+ | id | c_str | child_count | +----+-------+-------------+ |  1 | 玩      |           2 | |  4 | 学     |           3 | |  8 | 睡     |           0 | +----+-------+-------------+ 3 rows in set (0.00 sec) mysql>