-----------------------------------------------------------------------------------
--------------------------已优化的待开帐语句--------------------------------------------
------------------------------------------------------------------------------------
--优化版本
--把待开帐和已开帐的统计结果都取出,两个结果集 中去除已开帐的统计信息
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