/*dialect*/
SELECT * FROM
(
select
CASE WHEN '' = '銷售組' THEN to_char(SALEGROUP)
WHEN '' = '業務員' THEN to_char(xsy)
WHEN '' = '客戶型別' THEN to_char(KHLB)
END AS FZTJ,
KHLB,
xsy,
SJBM,
"上級客戶",
FNUMBER,
"二級客戶",
-- NVL(A2.FJSAMOUNT,0) AS "期初應收金額應收單",
"期初應收金額出庫單",
"期初實收金額",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初結欠余額應收單",
"期初結欠余額出庫單",
"發貨數量",
"發貨金額",
"本期實收金額",
"授信限額",
-- NVL(B1.QCKPJEYS,0) AS "期初未開票應收",
"期初未開票出庫",
-- "本期應收金額",
"本期已開票",
yfje,
BQHZ,
QCHZ,
"期初應收金額出庫單"+"發貨金額"-"本期實收金額"-nvl(BQHZ,0)-nvl(QCHZ,0) as "期末余額"
--Y1.QMHZ,
from (
SELECT
A1.xsy,
A1.SALEGROUP,
A1.FParentID,
CASE WHEN A1.SJBM IS NULL THEN A1.FNUMBER ELSE A1.SJBM END AS SJBM,
CASE WHEN A1.SJKH IS NULL THEN A1.FNAME_L2 ELSE A1.SJKH END AS "上級客戶",
-- A1.SJBM,
-- A1.SJKH AS "上級客戶",
A1.FID,
A1.FNUMBER,
A1.FNAME_L2 AS "二級客戶",
A1.KHLB,
-- NVL(A2.FJSAMOUNT,0) AS "期初應收金額應收單",
NVL(A22.FJSAMOUNT,0) AS "期初應收金額出庫單",
NVL(A3.FActualAMOUNT,0) AS "期初實收金額",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初結欠余額應收單",
(NVL(A22.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初結欠余額出庫單",
NVL(A4.FQTY,0) AS "發貨數量",
NVL(A4.HSJE,0) AS "發貨金額",
NVL(A9.FActualAMOUNT,0) AS "本期實收金額",
NVL(A10.xye,0) AS "授信限額",
-- NVL(B1.QCKPJEYS,0) AS "期初未開票應收",
NVL(BB1.QCKPJECK,0) AS "期初未開票出庫",
NVL(AA.BQYSAMOUNT,0) AS "本期應收金額",
NVL(B2.BQKPJE,0) AS "本期已開票",
yf.yfje,
Y.BQHZ,Y2.QCHZ
--Y1.QMHZ,
--期初未開票+本期應收-本期開票=期末未開票
FROM
(
--客戶
select distinct AA.FID,AA.FNUMBER,AA.FNAME_L2,AA.FParentID,
CASE WHEN BB.FID IS NULL THEN AA.FID ELSE BB.FID END AS SJFID,
BB.FNUMBER AS SJBM,BB.FNAME_L2 AS SJKH,b.xsy,e.fname_l2 as SALEGROUP,
Q.DW AS KHLB
from T_BD_Customer AA
left join khlb Q on Q.FID = AA.FID
--left join T_BD_CSSPGroup Q ON Q.FID =AA.FBrowseGroupID
LEFT JOIN (select AA.FID,AA.FNUMBER,AA.FNAME_L2
from T_BD_Customer AA where AA.FControlUnitID= '00000000-0000-0000-0000-000000000000CCE7AED4') BB ON BB.FID = AA.FParentID
left join (
SELECT a.*,c.fname_l2 as xsy FROM T_BD_CustomerSaleInfo a
left join T_BD_CustomerSaler b on a.fid = b.FCustomerSaleID
left join T_BD_Person c on c.fid = b.FPersonID
where a.FSaleOrgID = 'sREAAAAAB9LM567U'
) b on b.FCustomerID = AA.fid
left join T_BD_SALEGROUP e on e.fid = b.CFSALEGROUPID
where
( b.CFSalegroupID in ( '') OR null IS NULL ) and
( b.FSaleOrgID= 'sREAAAAAB9LM567U' ) and
AA.FControlUnitID='00000000-0000-0000-0000-000000000000CCE7AED4'
) A1
LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,SUM(BQHZ) AS BQHZ
FROM
(
---本期的應收沖應付---
select
b.FAcctCussID as FAcctCussentID,
sum(b.FThisVerificateAmtLoc) as BQHZ
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
( b.FCurrencyID = '' OR null IS NULL )
and b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD')>= '2016-04-01'
and to_char(a.FBizDate,'YYYY-MM-DD')<= '2017-04-03'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
)
GROUP BY FAcctCussentID
) Y ON A1.FID =Y.FAcctCussentID
/*LEFT JOIN (
--壞賬損失
select T1.FAcctCussentID,0 AS SL,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QMHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <='2017-04-03'
AND (T3.FNUMBER ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
) Y1 ON A1.FID =Y1.FAcctCussentID*/
LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,sum(QCHZ) as QCHZ
FROM (
--壞賬損失
select T1.FAcctCussentID,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') >= '2016-04-01'
and TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <= '2017-04-03'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
---期初的應收沖應付---
--union all
--select
-- b.FAcctCussID,
-- sum(b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
--from T_AR_VerificationBill a
--inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
--where
-- ( b.FCurrencyID = '' OR null IS NULL )
--and b.FBillSubType = 2
--AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
--and a.FVerificationType in (103,109)
--and a.FControlUnitID = 'sREAAAAAB9LM567U'
--group by b.FAcctCussID
)
group by FAcctCussentID
) Y2 ON A1.FID =Y2.FAcctCussentID
LEFT JOIN
(
--起初應收余額（應收單）
select T11.FAsstActID,SUM(T11.FJSAMOUNT) AS FJSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS FJSAMOUNT
from T_AR_OtherBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and FCompanyID = 'sREAAAAAB9LM567U'
union all
SELECT T1.FAsstActID,
T2.FRecievePayAmountLocal AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID where ( T1.FBillStatus =3 )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
) T11
GROUP BY T11.FAsstActID
) A2 ON A1.FID = A2.FAsstActID
LEFT JOIN
(
--起初應收余額（出庫單）
--1、其他應收單+銷售出庫單
select
FAsstActID,
SUM(FJSAMOUNT) AS FJSAMOUNT
from (
SELECT FAsstActID,SUM(FAmountLocal) AS FJSAMOUNT
FROM OtherBill_Define
WHERE FCompanyID = 'sREAAAAAB9LM567U'
GROUP BY FAsstActID
union all
---深圳的初始化銷售出庫單
SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACUbM567U'
GROUP BY T1.FCustomerID
union all
---上海的初始化銷售出庫單
SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACIzM567U'
GROUP BY T1.FCustomerID
union all
select FBalanceCustomerID as FAsstActID,SUM(FLocalAmount) AS FJSAMOUNT
from T_IM_SaleIssueBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and NVL(fisinitbill,0) = 0
and
( FStorageOrgUnitID = 'sREAAAAAB9LM567U')
GROUP BY FBalanceCustomerID
union all
--壞賬損失
select T1.FAcctCussentID,
SUM(-T1.FBadAmountsLocal)-SUM(-T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') < '2016-04-01'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
union all
select
b.FAcctCussID,
sum(-b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
union all
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerID
--預返剔除（電器）
--初始化應收單+期初出庫單
union all
SELECT T1.FAsstActID,
SUM(-T2.FRecievePayAmountLocal) AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID
where ( T1.FBillStatus =3 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
AND T1.FIsInitializeBill = 1
GROUP BY T1.FAsstActID
union all
--剔除預返
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(-T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerID
union all
--電器期初發出商品
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAADZGt7M567U')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and T1.fisinitbill = 1
GROUP BY T2.FBalanceCustomerID
union all
--固定資產清理
select
A.FAsstActID,
SUM(B.FRecievePayAmountLocal) AS FAmountLocal
from T_AR_OtherBill A
INNER JOIN T_AR_OtherBillentry B ON A.FID = B.FPARENTID
WHERE B.FExpenseItemID = 'bjWzSkEJABjgU38AAAEAGHjkvJQ='
AND A.FCompanyID = 'sREAAAAAB9LM567U'
AND to_char(A.FBizDate,'YYYY-MM-DD') < '2016-04-01'
GROUP BY A.FAsstActID
)
group by FAsstActID
) A22 ON A1.FID = A22.FAsstActID
LEFT JOIN
(
select T11.FAsstActID,SUM(T11.BQYSAMOUNT) AS BQYSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS BQYSAMOUNT
from T_AR_Ot
uj5u.com熱心網友回復：
看的眼花繚亂
你的好幾個union all里面都是無任何過濾條件或者過濾資料量太大（時間條件<2016年4月）的統計SQL，這種SQL沒什么好優化的，乖乖地掃描整表吧。如果非要優化，前面有ID類條件的，也需可以使用索引優化一下。
uj5u.com熱心網友回復：
老老實實弄個報表庫,定期出報表想靠一個服務器包打天下不現實哇
uj5u.com熱心網友回復：
做磁區表，查找快uj5u.com熱心網友回復：
沒有你的資料和表結構，很難優化啊。建議您還是自己學習以下相關的優化技巧吧。uj5u.com熱心網友回復：
用滑輪滾都滾了好幾下，這樣的SQL真心看著累啊
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39943.html
標籤：高級技術