APSIS_PART 产品主表
SALES_POLICY_BASE_PRICE 价格基本表
SALES_POLICY_PRICE 价格政策表
SALES_POLICY_PRICE_ENTRY 产品价格表
DISTRIBUTION_CHANNEL_CODE为“002”的，取价格政策 为“apsis001 ” 且 EFFECTIVE_DATE最大的一条 PRICE
DISTRIBUTION_CHANNEL_CODE为“001”的，取价格政策 为“2sOC” 且 EFFECTIVE_DATE最大的一条 PRICE
DISTRIBUTION_CHANNEL_CODE为“001”的，取价格政策 为“Ds2sSC”或“2sSC” 且 EFFECTIVE_DATE最大的一条 PRICE
SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC
------解决思路----------------------
LZ 能提供一些基础数据吗？ 不大好分析 。
------解决思路----------------------
CASE WHEN 部分能否抽像成 WITH？
------解决思路----------------------
列出执行计划看看
------解决思路----------------------
SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
t1.PRICE
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t2.PRICE
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t3.PRICE
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
) t1
ON t1.MATERIAL_ID = AP.ID AND t1.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
) t2
ON t2.MATERIAL_ID = AP.ID AND t2.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
) t3
ON t3.MATERIAL_ID = AP.ID AND t3.rn = 1
ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC