pid 递归总计所有子类数量，如何写sql？

Question

drop table T_SORT;
create table T_SORT
(
  PK_AUTO_ID        VARCHAR2(20) not null,
  PID               VARCHAR2(20),
  NAME              VARCHAR2(500) not null,
  SORT              VARCHAR2(20),
  CHECK_FLAG        VARCHAR2(2) not null,
  PUBLIC_TAG        VARCHAR2(2) not null,
  FK_TEMPLATE_ID    VARCHAR2(500),
  FK_POST_USER_ID   VARCHAR2(20),
  POST_TIME         DATE,
  FK_UPDATE_USER_ID VARCHAR2(20),
  UPDATE_TIME       DATE,
  IS_FINAL          NUMBER default -1 not null,
  INDEX_NUM         NUMBER,
  STATUS            NUMBER not null,
  VALID_TIME        VARCHAR2(20)
);
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9991', '9990', '一级分类', null, '0', '0', null, '1', to_date('30-03-2010', 'dd-mm-yyyy'), null, null, -1, null, 1, '2010-04-03');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9996', '9992', '三级分类', null, '0', '0', null, '1', to_date('30-03-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-04-02');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9992', '9991', '二级分类', null, '0', '0', null, '1', to_date('30-04-2010', 'dd-mm-yyyy'), null, null, -1, null, 1, '2010-03-01');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9994', '9992', '三级分类2', null, '0', '0', null, '1', to_date('30-03-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-04-02');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9993', '9992', '三级分类3', null, '0', '0', null, '1', to_date('31-03-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-04-02');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9995', '9992', '三级分类4', null, '0', '0', null, '1', to_date('01-04-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-03-02');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9998', '9991', '三级分类4', null, '0', '0', null, '1', to_date('01-04-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-04-02');
insert into T_SORT (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9999', '9991', '二级分类4', null, '0', '0', null, '1', to_date('01-04-2010', 'dd-mm-yyyy'), null, null, 1, null, 1, '2010-04-02');
commit;上面是测试数据，pid为pk_auto_id的父id，我现在需要统计，每一个主键id下面到底有多少个子分类、小子分类、小子子分类。写一条sql，需要获取的数据实例如下：
--编号--主键id----名称--------所有子类数量(包括自己)
1 9991 一级分类     8
2 9992 二级分类     5
3 9996 三级分类     1
4 9994 三级分类2     1
5 9993 三级分类3     1
6 9995 三级分类4     1
7 9998 三级分类4     1
8 9999 二级分类4     1这个该如何实现，想了好久，没有思路啊？

Answer 1

SELECT pk_auto_id,
  NAME,
  (SELECT COUNT(1)
  FROM t_sort
    START WITH pk_auto_id      =t.pk_auto_id
    CONNECT BY PRIOR pk_auto_id=pid
  )counts
FROM T_SORT t

Answer 2

SELECT a.pk_auto_id,
       a.pid,
       a.name,
       (SELECT COUNT(*)
          FROM t_sort t
         START WITH t.pk_auto_id = a.pk_auto_id
        CONNECT BY PRIOR t.pk_auto_id = t.pid) cnt
  FROM t_sort a
 ORDER BY a.pk_auto_id;

Answer 3

呵呵，临睡时来看一眼
还有种写法
SELECT pk_auto_id,
  name,
  COUNT(1) counts
FROM
  (SELECT CONNECT_BY_ROOT pk_auto_id pk_auto_id,
    CONNECT_BY_ROOT NAME NAME
  FROM T_SORT t
    CONNECT BY PRIOR pk_auto_id=pid
  )
GROUP BY pk_auto_id,
  name;
本来可以直接在内层group by connect_by_root ..的，今天一直报不是group by表达式。难道是bug..

Answer 4

你这个太耗时了啊，我正式表t_sort_deppart里面有3千多万条记录。这样就卡死了，10分钟了还出不来结果，这个sql能优化下不？

Answer 5

每条记录都去遍历一遍，还是3000万的数据..没什么好的办法
建好 主键id和pid列上的索引