如何优化这条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

[解决办法]

SQL code

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,