已经解决啦,谢谢楼上的各位,下面是两种解决方案
一、关联查询
SELECT a.CODE AS PARENT_CODE, b.CODE AS CHILD_CODE, COUNT(c.CODE) AS SUBNUM
FROM the_table a, the_table b, the_table c
where a.ID = b.PARENT_ID and b.ID = c.PARENT_ID(+) and a.CODE='100'
GROUP BY a.CODE b.CODE
(where条件中的加号是外连接,在有的数据库产品中是用星号*表示外连接的)
二、嵌套查询
SELECT a.CODE AS PARENT_CODE, b.CODE AS CHILD_CODE,
(SELECT COUNT(b.CODE) FROM the_table b
WHERE b.PARENT_ID = a.ID)
as SUBNUM
FROM the_table a