日期:2014-05-17 浏览次数:20952 次
WITH t AS ( SELECT 'A' switch_code, 'u' status FROM dual UNION ALL SELECT 'A' , 'r' status FROM dual UNION ALL SELECT 'A' , 'd' status FROM dual UNION ALL SELECT 'A' , 'a' status FROM dual UNION ALL SELECT 'A' , 'd' status FROM dual UNION ALL SELECT 'A' , 'u' status FROM dual UNION ALL SELECT 'A' , 'u' status FROM dual UNION ALL SELECT 'A' , 'u' status FROM dual ) SELECT switch_code, SUM(decode(status,'u',1,0))/COUNT(switch_code) u占有率, SUM(decode(status,'a',1,0))/COUNT(switch_code) a占有率, SUM(decode(status,'d',1,0))/COUNT(switch_code) d占有率, SUM(decode(status,'r',1,0))/COUNT(switch_code) r占有率 FROM t GROUP BY switch_code --result: A 0.5 0.125 0.25 0.125
------解决方案--------------------
select sum(decode(status,'U',1,0))/count(1)*100 from switch_port c group by switch_code