产品统计报表的sql(oracle)

select tb_total.*
  from (select pros.*, rownum RN
          from (select p.product_code,
                       p.product_name,
                       p.product_type_name,
                       nvl(rd.recommend_total, 0) recommend_total,
                       nvl(rd.open_suc_total, 0) open_suc_total,
                       to_char(nvl(rd.recommend_rat, 0), 'fm9999990.00') recommend_rat,
                       ration(nvl(rd.recommend_total, 0), g_recommend_total) recommend_zb,
                       nvl(rd.online_point, 0) online_point,
                       nvl(rd.vaild_point, 0) vaild_point,
                       ration(nvl(rd.online_point, 0), g_online_point) online_point_zb,
                       to_char(ration(nvl(rd.vaild_point, 0), g_vaild_point),
                               'fm9999990.00') vaild_point_zb,
                       nvl(rd.valid_user, 0) valid_user,
                       to_char(ration(nvl(rd.valid_user, 0), g_valid_user),
                               'fm9999990.00') valid_user_zb,
                       userinfo.user_count,
                       g_rd.g_valid_user,
                       g_rd.g_vaild_point
                  from (select rownum RN,
                               p.product_code,
                               p.product_name,
                               pt.product_typeid,
                               pt.product_type_name
                          from tb_product_type_relating re,
                               tb_product_type          pt,
                               tb_product_info          p
                         where re.typeid = pt.product_typeid(+)
                           and p.product_code = re.pid(+)) p,
                       (select d.product_code,
                               sum(d.recommend_total) recommend_total,
                               sum(d.open_suc_total) open_suc_total,
                               ration(sum(d.open_suc_total),
                                      sum(d.recommend_total)) recommend_rat,
                               count(distinct d.organise_id) online_point,
                               count(distinct case
                                       when d.open_suc_total > 0 then
                                        d.organise_id
                                       else
                                        null
                                     end) vaild_point,
                               count(distinct case
                                       when d.open_suc_total > 0 then
                                        d.emp_no
                                       else
                                        null
                                     end) valid_user
                          from stat_recomend_day d
                         where 1 = 1
                           and 1 = 1
                           and d.organise_code like 'SCAA%'
                         group by d.product_code) rd,
                       (select sum(recommend_total) g_recommend_total,
                               sum(online_point) g_online_point,
                               sum(vaild_point) g_vaild_point,
                               sum(valid_user) g_valid_user
                          from (select d.product_code,
                                       sum(d.recommend_total) recommend_total,
                                       sum(d.open_suc_total) open_suc_total,
                                       count(distinct d.organise_id) online_point,
                                       count(distinct case
                                               when d.open_suc_total > 0 then
                                                d.organise_id
                                               else
                                                null
                                             end) vaild_point,
                                       count(distinct case
                                               when d.open_suc_total > 0 then
                                                d.emp_no
                                               else
                                                null
                                             end) valid_user
                                  from stat_recomend_day d
                                 where 1 = 1
                                   and 1 = 1
                                   and d.organise_code like 'SCAA%'
                                 group by d.product_code) g_rd,
                               tb_product_info p
                         where p.product_code = g_rd.product_code(+)) g_rd,
                       (select count(t1.organise_code) user_count
                          from sys_organise_info t1, sys_user_info t2
                         where t1.organise_id = t2.organise_id
                           and t2.flag != '1'
                           and t1.organise_code like  'SCAA%') userinfo
                 where p.product_code = rd.product_code(+)
                 order by open_suc_total desc) pros) tb_total