oracle 求百分比

oracle求百分比

 

例子：求status为21的记录所占百分比

 

SELECT tt.retrieve retrievedrate,
       tc.公司名称 companyname,
       tc.部门名称 deptname,
       tc.用户名称 username,
       tc.nodename nodename,
       tc.processname processname
  FROM (SELECT round(SUM(CASE
                           WHEN t.status = '21' THEN
                            1
                           ELSE
                            0
                         END) / COUNT(*),
                     4) * 100 || '%' retrieve,
               t.node_id
          FROM losi_token t, losd_node t1, losd_process t2
         WHERE 
            t.node_id = t1.node_id
           AND t1.process_id = t2.process_id
           and t.receivetime > to_date('2011-05-12','yyyy-mm-dd')
           and t.receivetime < to_date('2013-01-12','yyyy-mm-dd')
         GROUP BY t.node_id) tt
  LEFT JOIN (SELECT tu.公司名称,
                    tu.部门名称,
                    tu.用户名称,
                    tu.用户id,
                    t3.node_id,
                    t3.nodename,
                    t4.processname
               FROM userAndCompany tu,
                    losd_node                t3,
                    losd_process             t4
              WHERE t3.process_id = t4.process_id
               ) tc
    ON (tt.node_id = tc.node_id)