Statistical analysis of the frequency of each character in a specific field.

    朋友给了一道测试题：
     用一条查询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