select parentid id ,count(id)childCont from table group by parentid
select t.id,t.name,(select count(1) from 表 where parentid = t.id) from 表 t
select id, (select count(1)-1 from t start with id=tt.id connect by prior id=parentid) from t tt
select id,count(*),max(name) from table
select id, (select count(1)-1 from t start with id=tt.id connect by prior id=parentid) from t tt 没看懂,能解释下吗?这是什么的写法。
select parentid ,count(distinct id) ,max(name) from table_name ; commit;
我怎么测试出来好像有点不对create table nexttest ( parentid varchar(4), childid varchar(4) ) SQL> select * from nexttest;PARENTID CHILDID ------------------------------ ------------------------------ a b a c a e b b1 b b2 c c1 e e1 e e3 d d1 d d110 rows selected.SQL> select childid,(select count(1)-1 from nexttest start with childid=bb.childid 2 connect by prior childid=parentid) hh 3 from nexttest bb;CHILDID HH ------------------------------ ---------- b 2 c 1 e 2 b1 0 b2 0 c1 0 e1 0 e3 0 d1 1 d1 110 rows selected.
回13楼 这个代码我没测试过,不过你的结果证明了我是对的`` 楼主的id你用childid代替了 唯一结果不正常的在childid='d1'的那两条记录 那是因为你两条记录重复了 一般下id是不会重复的,因此我将其忽略,否则就改成 select id, (select count(1) from t start with parentid=tt.id connect by prior id=parentid) from t tt
呵呵 看来是我理解错意思了 我的理解是“统计每个父ID下有多少个子ID” 就像下面这样的结果SQL> select * from nexttest2;PARE CHIL RN ---- ---- ---------- a b 1 a c 1 a e 1 b b1 1 b b2 1 c c1 1 d d1 1 e e1 1 e e3 19 rows selected.SQL> SQL> select parentid,count(*) 2 from nexttest2 3 group by parentid;PARE COUNT(*) ---- ---------- e 2 d 1 a 3 b 2 c 1SQL>
select parentId,count(childId) from child group by parentId
from 表 t
(select count(1)-1 from t start with id=tt.id connect by prior id=parentid)
from t tt
from table
(select count(1)-1 from t start with id=tt.id connect by prior id=parentid)
from t tt 没看懂,能解释下吗?这是什么的写法。
parentid
,count(distinct id)
,max(name)
from table_name
;
commit;
(
parentid varchar(4),
childid varchar(4)
)
SQL> select * from nexttest;PARENTID CHILDID
------------------------------ ------------------------------
a b
a c
a e
b b1
b b2
c c1
e e1
e e3
d d1
d d110 rows selected.SQL> select childid,(select count(1)-1 from nexttest start with childid=bb.childid
2 connect by prior childid=parentid) hh
3 from nexttest bb;CHILDID HH
------------------------------ ----------
b 2
c 1
e 2
b1 0
b2 0
c1 0
e1 0
e3 0
d1 1
d1 110 rows selected.
这个代码我没测试过,不过你的结果证明了我是对的``
楼主的id你用childid代替了
唯一结果不正常的在childid='d1'的那两条记录
那是因为你两条记录重复了
一般下id是不会重复的,因此我将其忽略,否则就改成
select id,
(select count(1) from t start with parentid=tt.id connect by prior id=parentid)
from t tt
可id里根本没有
这里是统计出每个id下有多少个子id
如果你要根据parentid来,稍微修改下
怎么改,自己思考下~
我的理解是“统计每个父ID下有多少个子ID”
就像下面这样的结果SQL> select * from nexttest2;PARE CHIL RN
---- ---- ----------
a b 1
a c 1
a e 1
b b1 1
b b2 1
c c1 1
d d1 1
e e1 1
e e3 19 rows selected.SQL>
SQL> select parentid,count(*)
2 from nexttest2
3 group by parentid;PARE COUNT(*)
---- ----------
e 2
d 1
a 3
b 2
c 1SQL>
select parentId,count(childId) from child group by parentId