select departid
  from depart d
 start with d.departid = '02'
connect by prior d.departid = d.parentid

select departName, count(d.DeptId) Qty
  from (select departid, departName
          from depart d
         start with d.departid = '02'
        connect by prior d.departid = d.parentid) a,
       peopleInfo p
 where a.departid = p.DeptId
 group by a.departName

--获取当前地区的顶级地区Id
function GetTopDepartId(departid varchar2) return varchar2 as
  pDepartId varchar2;
begin
  select c.departid
    into pDepartId
    from depart c
   where c.parent_id = '0'
   start with d.parentid = '0'
  connect by prior d.departid = d.parentid;

  return pDepartId;
end;

--获取顶级地区个性别的数
select d.departName,
       DECODE(peopleSex, 0, '女', '男') as Sex,
       sum(qty) as Qty
  from (select GetTopDepartId(departId) as Id, p.Qty
          from depart d,
               (select deptid, peopleSex, count(0) as qty
                  from peopleInfo
                 group by deptid, peopleSex) p
         where d.departId = p.deptid
         start with d.parentid = '0'
        connect by prior d.departid = d.parentid) a,
       depart d
 where d.departid = a.id
 group by d.departName, peopleSex

完成上面以后再做行转列就查处对应的结果