朋友给了一道测试题： 用一条查询SQL统计某个字段各字符及出现的次数，并按出现次数倒序排列。

咋一看感觉很难，认真想想发现也并不是很难。以下是代码示例：

with t1 as
(
select 'hhjdbgydgdjd' str from dual
union all
select 'jdujgbhdg' str from dual
union all
select '514dgj1fgy' str from dual
) --构建临时表

select t1.str,/*t1.str,t2.lv,*/substr(t1.str,t2.lv,1) alp,count(substr(t1.str,t2.lv,1)) cnt
from  t1
left join (select level lv from dual connect by level < 20) t2 on 1=1 --level的值视字段长度而定
where substr(t1.str,t2.lv,1) is not null
group by t1.str,substr(t1.str,t2.lv,1)
order by t1.str,count(substr(t1.str,t2.lv,1)) desc
;