1.end后面的 final_url是别名
2.utl_url.unescape(final_url,'utf-8') 是解码url
3.oracle10g开始数据库内建了符合ieee posix (portable operating system for unix)标准的正则表达式，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