Oracle的一条sql使用临时表进行查询

with CTE as(
  SELECT 
    C.SUB_CORP_NAME
    ,B.BUS_PATH_NAME
  	,ROUND(S.ACTUAL_DAY_RUN_TOTAL_MILE / 1000, 2) AS MILE_ALREADY
  	,ROUND(S.DAY_ALREADY_RUN_TOTAL_MILE / 1000, 2) AS CURRENT_MILE
  	,DECODE(S.DAY_ALREADY_RUN_TOTAL_MILE, 0, 0, ROUND(S.ACTUAL_DAY_RUN_TOTAL_MILE / S.DAY_ALREADY_RUN_TOTAL_MILE * 100 ,2)) as RATE
  ,rank() over (order by DECODE(S.DAY_ALREADY_RUN_TOTAL_MILE, 0, 0, ROUND(S.ACTUAL_DAY_RUN_TOTAL_MILE / S.DAY_ALREADY_RUN_TOTAL_MILE * 100 ,2))) rn
  FROM NBBUSITS.T_BUS_PATH_STA_DATA S,NBBUSITS.T_BUS_PATH  B,NBBUSITS.T_BUS_PATH_COMPANY C 
  WHERE 1=1
    AND UPDATE_TIME > to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') 
    AND S.BUS_PATH_ID = B.BUS_PATH_ID 
    AND B.BUS_PATH_NAME = C.BUS_PATH_NAME 
    and C.SUB_CORP_NAME like '永平%'
    and S.ACTUAL_DAY_RUN_TOTAL_MILE > 0 and S.DAY_ALREADY_RUN_TOTAL_MILE > 0
)
select b.* from CTE a
left join(
  SELECT 
    T.BUS_PATH_NAME AS CATEGORY
    ,DECODE(SERIES,'CURRENT_MILE','计划','MILE_ALREADY','实际','RATE','完成率') AS SERIES
    ,value
  from CTE UNPIVOT(value for SERIES in (MILE_ALREADY, CURRENT_MILE, RATE)) T 
  LEFT JOIN NBBUSITS.T_ON_DUTY_INFO D ON T.BUS_PATH_NAME = D.SUB_CORP_NAME
)B
on a.BUS_PATH_NAME = B.category
WHERE 1=1
	and RN <= 10
order by rn,CATEGORY,SERIES

使用到第一个知识点，with xxx as (),将一条sql语句的结果作为一个临时变量（临时表）存在xxx，然后在下面就可以将xxx当成表来查询使用，适用在复杂的又只想用一条sql完事的需求。因本案例需要使用到列转行，并且需要在结果中以原本的某一列做排序，故用到with xxx as，

另外用到列转行UNPIVOT、跳跃排序rank() over(order by...)、decode等函数

做为笔记记录，另拓展了解dense_rank() over()、min()/max() over()、lead()/lag() over()、