select MID,COUNT(1) from POST CONNECT BY PRIOR PID=MID GROUP BY MID
前面语句有问题,是固定两层吗?select a.mid,a.name,a.type,count(1) from modul a,post b where a.mid=b.mid or a.mid=b.pid group by a.mid,a.name,a.type
表设计不合理,一般父子关系 名称:modul(论坛模块表) 字段:mid, name,parentmid 1,aa,0 2,ab,1 3,ac,1 4,aba,2 5,ba,0 6,bb,5 表2 名称:post(帖子表) 字段:pid,pname,mid (论坛模块表的mid) 1,paa1,1 2,paa2,1 3,pab1,2 4,pac1,3 5,pac2,3 6,pac3,3 7,paba1,4 8,pba1,5 9,pba2,5 查aa模块下的所有帖子数: oracle: select count(*) as cnt from (select t.* from modul t start with t.name = 'aa' connect by prior t.mid = t.parentmid) x inner join post s on x.mid = s.mid;
开始给点表结构都是错的,能写对了我就成神仙了,呵呵,重新给你写吧with T AS ( select MID,name,parentmid,TYPE,CONNECT_BY_ROOT(MID) SID from modul CONNECT BY PRIOR parentId=MID) SELECT T.MID,MAX(T.name),MAX(T.parentmid),MAX(T.TYPE),COUNT(1) FROM T,POST WHERE T.SID=POST.MID GROUP BY T.MID
from POST
CONNECT BY PRIOR PID=MID
GROUP BY MID
from modul a,post b
where a.mid=b.mid or a.mid=b.pid
group by a.mid,a.name,a.type
名称:modul(论坛模块表)
字段:mid, name,parentmid
1,aa,0
2,ab,1
3,ac,1
4,aba,2
5,ba,0
6,bb,5
表2
名称:post(帖子表)
字段:pid,pname,mid (论坛模块表的mid)
1,paa1,1
2,paa2,1
3,pab1,2
4,pac1,3
5,pac2,3
6,pac3,3
7,paba1,4
8,pba1,5
9,pba2,5
查aa模块下的所有帖子数:
oracle:
select count(*) as cnt
from (select t.*
from modul t
start with t.name = 'aa'
connect by prior t.mid = t.parentmid) x
inner join post s on x.mid = s.mid;
表1
名称:modul(论坛模块表)
字段:mid, name,parentId,type // type值代表两种类型:1,父模块,2字模块
你好,
要显示出所有父模块下的帖子数(group by),不是指定aa模块名,
我公司用的是mysql数据库,该怎么查呢??
麻烦您把我再定一个吧。。谢谢啦!~~~
select MID,name,parentmid,TYPE,CONNECT_BY_ROOT(MID) SID
from modul
CONNECT BY PRIOR parentId=MID)
SELECT T.MID,MAX(T.name),MAX(T.parentmid),MAX(T.TYPE),COUNT(1)
FROM T,POST
WHERE T.SID=POST.MID
GROUP BY T.MID