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
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_SIX
from 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_AMOUNT
left 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
例
(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,
如果是这种SQL我的习惯一般是写成存储过程。
那样看起来更加清晰。
你这样做。。那以后维护起来就是一个大问题。
全部都是嵌套。。