Oracle中的case語句

1.END後面的 final_url是別名

2.UTL_URL.unescape(final_url,'UTF-8') 是解碼url

3.Oracle10g開始資料庫內建了符合IEEE POSIX (Portable Operating System for Unix)標準的Regex，10g一下的版本除了like不支援正則，所以?\&要用兩個when

select   *   from (

    select UTL_URL.unescape(final_url,'UTF-8') as url,sum(list_pv_count) as total_pv from (

          select

            CASE

              WHEN INSTR(list_url,'?',28)>0 THEN   SUBSTR( list_url,0,(INSTR(list_url,'?',28)-1))

              WHEN INSTR(list_url,'&',28)>0 THEN   SUBSTR( list_url,0,(INSTR(list_url,'&',28)-1))

            ELSE list_url

            END final_url,

                      list_url,list_pv_count  from T_LOG_CHANNEL_TOP_LIST

          where list_date between to_date('20110629 00:00:00', 'yyyymmdd hh24:mi:ss') and to_date('20110629 23:59:59', 'yyyymmdd hh24:mi:ss') and list_channel_constant='BAIKE'

      )group by final_url order by total_pv desc  

 ) where rownum<201;