如何优化这条select语句!
SELECT DISTINCT (SELECT S.BUSINNAME
FROM BNDICT_T_DICTIONARY S
WHERE S.BUSINTYPEID = 'WH_CITY'
AND S.BUSINID = P.CITY) CITY,
P.COUNTRY_AREA,
(SELECT S.BUSINNAME
FROM BNDICT_T_DICTIONARY S
WHERE S.BUSINTYPEID = 'WH_ACCOUNT_TYPE'
AND S.BUSINID = P.Account_Type) ACCOUNT_TYPE,
(SELECT S.BUSINNAME
FROM BNDICT_T_DICTIONARY S
WHERE S.BUSINTYPEID = 'WH_YS_PROJECT'
AND S.BUSINID = P.PROJECT) PROJECT,
P.COST_NAME,
P.PROVISION_AMOUNT,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '1'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_ONE,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '1'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_ONE,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '1'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_ONE,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '2'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_TWO,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '2'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_TWO,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '2'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_TWO,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '3'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_THREE,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '3'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_THREE,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '3'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_THREE,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '4'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_FOUR,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '4'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_FOUR,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '4'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_FOUR,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '5'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_FIVE,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '5'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_FIVE,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '5'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_FIVE,
(SELECT PO.PAY_TIME
FROM abc PO
WHERE PO.PAY_BATCH = '6'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) TIME_SIX,
(SELECT PO.PAY_AMOUNT
FROM abc PO
WHERE PO.PAY_BATCH = '6'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) AMOUNT_SIX,
(SELECT PO.NOTE
FROM abc PO
WHERE PO.PAY_BATCH = '6'
AND P.CITY = PO.CITY
AND P.COUNTRY_AREA = PO.COUNTRY_AREA
AND P.PROJECT = PO.PROJECT
AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE
AND P.COST_NAME = PO.COST_NAME
AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNT) NOTE_SIX
FROM abc P
WHERE 1 = 1
[解决办法]
SELECT S.BUSINNAME CITY,P.COUNTRY_AREA,S.BUSINNAME ACCOUNT_TYPE,S.BUSINNAME PROJECT, P.COST_NAME,P.PROVISION_AMOUNT, (case when PO.PAY_BATCH = '1' then PO.PAY_TIME else null end) TIME_ONE, (case when PO.PAY_BATCH = '1' then PO.PAY_AMOUNT else null end) AMOUNT_ONE, (case when PO.PAY_BATCH = '1' then PO.NOTE else null end) NOTE_ONE, (case when PO.PAY_BATCH = '2' then PO.PAY_TIME else null end) TIME_TWO, (case when PO.PAY_BATCH = '2' then PO.PAY_AMOUNT else null end) AMOUNT_TWO, (case when PO.PAY_BATCH = '2' then PO.NOTE else null end) NOTE_TWO, (case when PO.PAY_BATCH = '3' then PO.PAY_TIME else null end) TIME_THREE, (case when PO.PAY_BATCH = '3' then PO.PAY_AMOUNT else null end) AMOUNT_THREE, (case when PO.PAY_BATCH = '3' then PO.NOTE else null end) NOTE_THREE, (case when PO.PAY_BATCH = '4' then PO.PAY_TIME else null end) TIME_FOUR, (case when PO.PAY_BATCH = '4' then PO.PAY_AMOUNT else null end) AMOUNT_FOUR, (case when PO.PAY_BATCH = '4' then PO.NOTE else null end) NOTE_FOUR, (case when PO.PAY_BATCH = '5' then PO.PAY_TIME else null end) TIME_FIVE, (case when PO.PAY_BATCH = '5' then PO.PAY_AMOUNT else null end) AMOUNT_FIVE, (case when PO.PAY_BATCH = '5' then PO.NOTE else null end) NOTE_FIVE, (case when PO.PAY_BATCH = '6' then PO.PAY_TIME else null end) TIME_SIX, (case when PO.PAY_BATCH = '6' then PO.PAY_AMOUNT else null end) AMOUNT_SIX, (case when PO.PAY_BATCH = '6' then PO.NOTE else null end) NOTE_SIXfrom abc P left join abc P on P.CITY = PO.CITY AND P.COUNTRY_AREA = PO.COUNTRY_AREA AND P.PROJECT = PO.PROJECT AND P.ACCOUNT_TYPE = PO.ACCOUNT_TYPE AND P.COST_NAME = PO.COST_NAME AND P.PROVISION_AMOUNT = PO.PROVISION_AMOUNTleft join BNDICT_T_DICTIONARY S on (S.BUSINID = P.CITY and S.BUSINTYPEID = 'WH_CITY') or (S.BUSINID = P.Account_Type and S.BUSINTYPEID = 'WH_ACCOUNT_TYPE') or (S.BUSINID = P.PROJECT and S.BUSINTYPEID = 'WH_YS_PROJECT')--where 1=1
[解决办法]
你還可以用decode把case when then 替換掉，個人愛好吧，呵呵
例
(case when PO.PAY_BATCH = '1' then PO.PAY_TIME else null end) TIME_ONE,
改為
decode(PO.PAY_BATCH,'1',PO.PAY_TIME,null) TIME_ONE,
