oracle 递归查询

select t.* , rownum , rowid  from emp t  ;


select empno , job , 
decode(job ,'SALESMAN' , 'MY_SALE', 
       'MANAGER' , 'MY_MANAGER' , 
       'NO_KNOW'   
      ) my_job   
from  emp  ;


select job , decode(JOB , 'CLERK' , COUNT(1) , 0  ) CLERK  , 
             decode(JOB , 'SALESMAN' , COUNT(1) , 0) SALESMAN , 
             decode(JOB , 'PRESIDENT' , COUNT(1) , 0) PRESIDENT , 
             decode(JOB , 'MANAGER' , COUNT(1) , 0 ) MANAGER , 
             decode(JOB , 'ANALYST' , COUNT(1) , 0 ) ANALYST   
from  emp group by job   ;


-- 统计每个职务下有多少人
select sum(CLERK) CLERK ,sum(SALESMAN) SALESMAN , sum(PRESIDENT) PRESIDENT ,
sum(MANAGER) MANAGER , sum(ANALYST) ANALYST 
from ( 
    select job , decode(JOB , 'CLERK' , COUNT(1) , 0  ) CLERK  , 
                 decode(JOB , 'SALESMAN' , COUNT(1) , 0) SALESMAN , 
                 decode(JOB , 'PRESIDENT' , COUNT(1) , 0) PRESIDENT , 
                 decode(JOB , 'MANAGER' , COUNT(1) , 0 ) MANAGER , 
                 decode(JOB , 'ANALYST' , COUNT(1) , 0 ) ANALYST   
    from  emp group by job ) ;


-- 统计每个部门下的每个职务下有多少人 
select deptno, count (tclerk)  clerk, 
            count (tsalseman)  salseman, 
            count (tpresident) president, 
            count (tmanager)   manager ,
            count (tanalyst)   analyst
from ( select deptno, decode(job, 'CLERK', 'CLERK') tclerk,
                 decode(job, 'SALESMAN', 'SALESMAN') tsalseman,
                 decode(job, 'PRESIDENT', 'PRESIDENT') tpresident,
                 decode(job, 'MANAGER', 'MANAGER') tmanager ,
                 decode(job, 'ANALYST', 'ANALYST') tanalyst
       from emp)
group by deptno;


-- 或者 
select deptno, count (tclerk)  clerk, 
            count (tsalseman)  salseman, 
            count (tpresident) president, 
            count (tmanager)   manager ,
            count (tanalyst)   analyst
from ( select deptno, decode(job, 'CLERK', 1 , null ) tclerk,
                 decode(job, 'SALESMAN', 1 ,null ) tsalseman,
                 decode(job, 'PRESIDENT', 1 , null) tpresident,
                 decode(job, 'MANAGER', 1 ,null) tmanager ,
                 decode(job, 'ANALYST', 1 , null) tanalyst
       from emp)
group by deptno;


-- 或者 (这种是最好的)
select deptno, sum (tclerk)  clerk, 
            sum (tsalseman)  salseman, 
            sum (tpresident) president, 
            sum (tmanager)   manager ,
            sum (tanalyst)   analyst
from ( select deptno, decode(job, 'CLERK', 1 , 0 ) tclerk,
                 decode(job, 'SALESMAN', 1 ,0 ) tsalseman,
                 decode(job, 'PRESIDENT', 1 , 0) tpresident,
                 decode(job, 'MANAGER', 1 ,0) tmanager ,
                 decode(job, 'ANALYST', 1 , 0) tanalyst
       from emp)
group by deptno;


-- select count(null) from dual  返回 为 0 ;
-- select count(具体列名) from xxx , 如果具体列名为空 , count 不会统计为一条记录 ;




-- oracle 递归 + 子查询 例子 




select  distinct   gb.*,
         (select ccc.id from  com_category ccc where   ccc.category_level = 1    start with  ccc.id = cc.id   connect by prior ccc.parent_id = ccc.id) level_one_category_id 
from goo_brand  gb   inner join  goo_product_brand     gpb   on   gb.id = gpb.brand_id 
                     inner join  goo_category_product  gct   on   gpb.product_id = gct.product_id 
                     inner join  com_category          cc    on   gct.category_id = cc.id   where  gb.display=1 ;
     
    
-- oracle 中的递归查询 
-- 根据父节点 , 查询该节点下的孩子节点         
select cwc.* from  com_wearing_category  cwc   where  cwc.status = 1   start with  cwc.id = 1502030932160000
       connect by prior  cwc.id = cwc.parent_id ; -- 作为父亲去查询孩子




--根据孩子节点 , 查询该节点上的父节点            
select cwc.* from  com_wearing_category  cwc   where  cwc.status = 1    start with cwc.id=1502031120430001 
       connect by prior  cwc.parent_id = cwc.id ; -- 作为孩子去查询父亲