sql的强大

2009-05-16 368 0

我知道sql的查询很复杂,也是很强大,但就没有见过如果强大的,让大家看一句我最近刚写的,就一句sql语句将总数,百分比都出来了!

select t.*,

round(decode(nvl(all_count,0),0,0,send_count/all_count),3)*100 send_count_rate,

round(decode(nvl(all_count,0),0,0,distribute_count/all_count),3)*100 distribute_count_rate,

round(decode(nvl(all_count,0),0,0,success_count/all_count),3)*100 success_count_rate,

round(decode(nvl(all_count,0),0,0,resend_count/all_count),3)*100 resend_count_rate,

round(decode(nvl(all_count,0),0,0,failure_count/all_count),3)*100 failure_count_rate

from

(

select company_name,

sum(case when process_state=1 or process_state=2 or process_state=3 or process_state=4 or process_state=5 then 1 else 0 end) all_count,

sum(case when process_state=1 then 1 else 0 end) send_count,

sum(case when process_state=2 then 1 else 0 end) distribute_count,

sum(case when process_state=3 then 1 else 0 end) success_count,

sum(case when process_state=4 then 1 else 0 end) resend_count,

sum(case when process_state=5 then 1 else 0 end) failure_count

from t_pbb_open_task group by company_name

) t

http://lh4.ggpht.com/_uwC7KS2yb2k/Sg2dfuB4pyI/AAAAAAAABok/sIn1ZczVVCg/s800/sql.JPG

相关文章

15年来的手艺之路:手艺人赵鹏的自述
纪念 Google 25 周年:从搜索引擎到科技巨头的演变之路
1小时编写一个支持七牛上传的 markdown 客户端3(打包发布篇)
1小时编写一个支持七牛上传的 markdown 客户端2(代码优化篇)
1小时编写一个支持七牛上传的 markdown 客户端1(技术实现篇)
从 wordpress 转移到 hexo

Leave a Reply