oracle 随记

SELECT a.tax_num,
       a.site_code,
       a.site_name,
       unionTable.operated_by,
       unionTable.operation_date as RQ,
       count(decode(unionTable.operation_type, 'NORMAL', 1, null)) DRZCFPS,
       count(decode(unionTable.operation_type, 'REFUND', 1, null)) DRTPS,
       count(decode(unionTable.operation_type, 'INVALID', 1, null)) DRFPS,
       sum(unionTable.ZCPFLJE) ZCPFLJE,
       sum(unionTable.TPFLJE) TPFLJE,
       sum(unionTable.FPFLJE) FPFLJE,
       imd.dept_cd,
       imd.dept_name
  FROM (SELECT b.invoice_code,
               b.invoice_num,
               b.operation_type,
               max(b.order_num) as order_num,
               to_char(b.operation_date, 'yyyy-mm-dd') as operation_date,
               b.operated_by,
               b.sales_belong_comp,
               SUM(DECODE(b.operation_type,
                          'NORMAL',
                          nvl(e.final_price * e.qty, 0) +
                          nvl(e.detail_shipping_cost, 0) -
                          nvl(e.card_paid_partprice, 0) -
                          nvl(e.card_paid_shippingprice, 0),
                          0)) ZCPFLJE,
               SUM(DECODE(b.operation_type,
                          'REFUND',
                          (case
                            when e.final_price > 0 then
                             - (nvl(e.final_price * e.qty, 0) +
                                nvl(e.detail_shipping_cost, 0) -
                                nvl(e.card_paid_partprice, 0) -
                                nvl(e.card_paid_shippingprice, 0))
                            else
                             (nvl(e.final_price * e.qty, 0) +
                             nvl(e.detail_shipping_cost, 0) -
                             nvl(e.card_paid_partprice, 0) -
                             nvl(e.card_paid_shippingprice, 0))
                          end),
                          0)) TPFLJE,
               SUM(DECODE(b.operation_type,
                          'INVALID',
                          nvl(e.final_price * e.qty, 0) +
                          nvl(e.detail_shipping_cost, 0) -
                          nvl(e.card_paid_partprice, 0) -
                          nvl(e.card_paid_shippingprice, 0),
                          0)) FPFLJE
          FROM iv_order_invoice b, iv_order_detail e
         where b.order_num = e.order_num
           and b.ord_line_id = e.line_id
         group by b.operation_type,
                  b.invoice_code,
                  b.invoice_num,
                  to_char(b.operation_date, 'yyyy-mm-dd'),
                  b.operated_by,
                  b.sales_belong_comp
        ) unionTable,
       iv_mst_site a,
       iv_order_header c,
       iv_mst_department imd
 where a.site_code = unionTable.sales_belong_comp
   and imd.dept_cd = c.dept_cd
   and c.order_num = unionTable.order_num
 group by a.tax_num,
          a.site_code,
          a.site_name,
          unionTable.operated_by,
          unionTable.operation_date,
          imd.dept_cd,
          imd.dept_name;