待账单统计语句

-----------------------------------------------------------------------------------
--------------------------已优化的待开帐语句--------------------------------------------
------------------------------------------------------------------------------------
--优化版本
--把待开帐和已开帐的统计结果都取出，两个结果集 中去除已开帐的统计信息
WITH
 waitingBill as
(
 select t.inv_seller_cid invSellerCid,t.inv_seller_name invSellerName,t.debtor_cid debtorCid,t.debtor_name debtorName,to_char(t.bill_date,'yyyy-mm-dd') billDate,to_char(t.payoff_date,'yyyy-mm-dd') payoffDate,t.bill_plan billPlan,count(t.id_ticket) count,
    --已确认总金额
    sum(case
        when  t.status='C'
              then dc.bill_amt
              else 0
        end
     ) as confirmedAmt,
     ----已确认总数
      sum(case
        when  t.status='C'
              then 1
              else 0
        end
     ) as confirmedCount,
     --已确认订票总金额
       sum(case
        when  t.status='C' and t.orig_id_ticket is null
              then dc.bill_amt
              else 0
        end
     ) as billBookAmt,
     --已确认订票总数
     sum(case
        when  t.status='C' and t.orig_id_ticket is null
              then 1
              else 0
        end
     ) as billBookCount,
     --已确认退票总金额
     sum(case
        when  t.status='C' and t.orig_id_ticket is not null
              then dc.bill_amt
              else 0
        end
     ) as billReturnAmt,
     --已确认退票总数
     sum(case
        when  t.status='C' and t.orig_id_ticket is not null
              then 1
              else 0
        end
     ) as billReturnCount,
     --未确认订票总金额
      sum(case
        when  t.status!='C' and t.orig_id_ticket is null
              then t.amt
              else 0
        end
     ) as unconfirmedBillBookAmt,
     --未确认订票总数
     sum(case
        when  t.status!='C' and t.orig_id_ticket is null
              then 1
              else 0
        end
     ) as unconfirmedBillBookCount,
    --未确认退票总金额
     sum(case
        when  t.status!='C' and t.orig_id_ticket is not null
              then t.amt
              else 0
        end
     ) as unconfirmedBillReturnAmt,
    --未确认退票总数
     sum(case
        when  t.status!='C' and t.orig_id_ticket is not null
              then 1
              else 0
        end
     ) as unconfirmedBillReturnCount,
    --未确认总数
    sum(case
       when  t.status!='C'
           then 1
           else 0
       end
  )  as unconfirmedCount  from t_ticket t left join t_debtor_clear_list dc on t.id_ticket=dc.id_ticket
   where 1=1   and t.bill_date is not null
   --开帐日 在当前月中
    and to_char(t.bill_date,'yyyy-mm-dd')>='2009-06-01' and to_char(t.bill_date,'yyyy-mm-dd')<='2009-06-30'
    and t.bill_status='0'
    group by t.inv_seller_cid,t.inv_seller_name,t.debtor_cid,t.debtor_name, to_char(t.bill_date,'yyyy-mm-dd'),to_char(t.payoff_date,'yyyy-mm-dd'),t.bill_plan
),
billedBill as
(
 select a.* from  waitingBill a ,t_debtor_bill b
 where a.invSellerCid=b.bill_seller_cid and a.invSellerName=b.bill_seller_name and a.debtorCid=b.bill_debtor_cid and a.debtorName=b.bill_debtor_name and a.billDate=to_char(b.bill_date,'yyyy-mm-dd') and a.payoffDate=to_char(b.payoff_date,'yyyy-mm-dd') and a.billPlan=b.bill_plan
 )
--SELECT * FROM  waitingBill WHERE  billDate!=(SELECT  billDate from billedBill )
SELECT * FROM  waitingBill WHERE  (invSellerCid||invSellerName||debtorCid||debtorName||billDate||payoffDate||billPlan)!=(SELECT  (invSellerCid||invSellerName||debtorCid||debtorName||billDate||payoffDate||billPlan) from billedBill )

 

--原始版本
--把待开帐和已开帐的统计结果都取出，用 MINUS  在数据库内存中 去除已开帐的统计信息
select * from (select t.inv_seller_cid invSellerCid,t.inv_seller_name invSellerName,t.debtor_cid debtorCid,t.debtor_name debtorName,to_char(t.bill_date,'yyyy-mm-dd') billDate,to_char(t.payoff_date,'yyyy-mm-dd') payoffDate,t.bill_plan billPlan,count(t.id_ticket) count,
  --已确认总金额
  sum(case
      when  t.status='C'
            then dc.bill_amt
            else 0
      end
   ) as confirmedAmt,
   ----已确认总数
    sum(case
      when  t.status='C'
            then 1
            else 0
      end
   ) as confirmedCount,
   --已确认订票总金额
     sum(case
      when  t.status='C' and t.orig_id_ticket is null
            then dc.bill_amt
            else 0
      end
   ) as billBookAmt,
   --已确认订票总数
   sum(case
      when  t.status='C' and t.orig_id_ticket is null
            then 1
            else 0
      end
   ) as billBookCount,
   --已确认退票总金额
   sum(case
      when  t.status='C' and t.orig_id_ticket is not null
            then dc.bill_amt
            else 0
      end
   ) as billReturnAmt,
   --已确认退票总数
   sum(case
      when  t.status='C' and t.orig_id_ticket is not null
            then 1
            else 0
      end
   ) as billReturnCount,
   --未确认订票总金额
    sum(case
      when  t.status!='C' and t.orig_id_ticket is null
            then t.amt
            else 0
      end
   ) as unconfirmedBillBookAmt,
   --未确认订票总数
   sum(case
      when  t.status!='C' and t.orig_id_ticket is null
            then 1
            else 0
      end
   ) as unconfirmedBillBookCount,
  --未确认退票总金额
   sum(case
      when  t.status!='C' and t.orig_id_ticket is not null
            then t.amt
            else 0
      end
   ) as unconfirmedBillReturnAmt,
  --未确认退票总数
   sum(case
      when  t.status!='C' and t.orig_id_ticket is not null
            then 1
            else 0
      end
   ) as unconfirmedBillReturnCount,
  --未确认总数
  sum(case
     when  t.status!='C'
         then 1
         else 0
     end
)  as unconfirmedCount  from t_ticket t left join t_debtor_clear_list dc on t.id_ticket=dc.id_ticket
 where 1=1   and t.bill_date is not null
 --开帐日 在当前月中
  and to_char(t.bill_date,'yyyy-mm-dd')>='2009-06-01' and to_char(t.bill_date,'yyyy-mm-dd')<='2009-06-30'
  and t.bill_status='0'
   group by t.inv_seller_cid,t.inv_seller_name,t.debtor_cid,t.debtor_name,to_char(t.bill_date,'yyyy-mm-dd'),to_char(t.payoff_date,'yyyy-mm-dd'),t.bill_plan
 order by to_char(t.bill_date,'yyyy-mm-dd') asc,t.debtor_name desc)

--去除充分的统计信息
MINUS

 select a.* from (select t.inv_seller_cid invSellerCid,t.inv_seller_name invSellerName,t.debtor_cid debtorCid,t.debtor_name debtorName,to_char(t.bill_date,'yyyy-mm-dd') billDate,to_char(t.payoff_date,'yyyy-mm-dd') payoffDate,t.bill_plan billPlan,count(t.id_ticket) count,
  --已确认总金额
  sum(case
      when  t.status='C'
            then dc.bill_amt
            else 0
      end
   ) as confirmedAmt,
   ----已确认总数
    sum(case
      when  t.status='C'
            then 1
            else 0
      end
   ) as confirmedCount,
   --已确认订票总金额
     sum(case
      when  t.status='C' and t.orig_id_ticket is null
            then dc.bill_amt
            else 0
      end
   ) as billBookAmt,
   --已确认订票总数
   sum(case
      when  t.status='C' and t.orig_id_ticket is null
            then 1
            else 0
      end
   ) as billBookCount,
   --已确认退票总金额
   sum(case
      when  t.status='C' and t.orig_id_ticket is not null
            then dc.bill_amt
            else 0
      end
   ) as billReturnAmt,
   --已确认退票总数
   sum(case
      when  t.status='C' and t.orig_id_ticket is not null
            then 1
            else 0
      end
   ) as billReturnCount,
   --未确认订票总金额
    sum(case
      when  t.status!='C' and t.orig_id_ticket is null
            then t.amt
            else 0
      end
   ) as unconfirmedBillBookAmt,
   --未确认订票总数
   sum(case
      when  t.status!='C' and t.orig_id_ticket is null
            then 1
            else 0
      end
   ) as unconfirmedBillBookCount,
  --未确认退票总金额
   sum(case
      when  t.status!='C' and t.orig_id_ticket is not null
            then t.amt
            else 0
      end
   ) as unconfirmedBillReturnAmt,
  --未确认退票总数
   sum(case
      when  t.status!='C' and t.orig_id_ticket is not null
            then 1
            else 0
      end
   ) as unconfirmedBillReturnCount,
  --未确认总数
  sum(case
     when  t.status!='C'
         then 1
         else 0
     end
)  as unconfirmedCount  from t_ticket t left join t_debtor_clear_list dc on t.id_ticket=dc.id_ticket
 where 1=1   and t.bill_date is not null
 --开帐日 在当前月中
  and to_char(t.bill_date,'yyyy-mm-dd')>='2009-06-01' and to_char(t.bill_date,'yyyy-mm-dd')<='2009-06-30'
  and t.bill_status='0'
   group by t.inv_seller_cid,t.inv_seller_name,t.debtor_cid,t.debtor_name,to_char(t.bill_date,'yyyy-mm-dd'),to_char(t.payoff_date,'yyyy-mm-dd'),t.bill_plan
 order by to_char(t.bill_date,'yyyy-mm-dd') asc,t.debtor_name desc) a,
 (
 select db.*
 from t_debtor_bill db where db.bill_status=1) b
 -- where a.invSellerCid!=b.bill_seller_cid and a.invSellerName!=b.bill_seller_name and a.debtorCid!=b.bill_debtor_cid and a.debtorName!=b.bill_debtor_name and a.billDate!=b.bill_date and a.payoffDate!=b.payoff_date and a.billPlan!=b.bill_plan
where a.invSellerCid=b.bill_seller_cid and a.invSellerName=b.bill_seller_name and a.debtorCid=b.bill_debtor_cid and a.debtorName=b.bill_debtor_name and a.billDate=to_char(b.bill_date,'yyyy-mm-dd') and a.payoffDate=to_char(b.payoff_date,'yyyy-mm-dd') and a.billPlan=b.bill_plan