SELECT MAX(A.EFFECTIVE_MONTH) EFFECTIVE_MONTH,
MAX(A.EMP_ID) EMP_ID,
MAX(A.WAGES) WAGES,
MAX(A.PROMOTION_WAGES) PROMOTION_WAGES,
MAX(ROUND(A.PROMOTION_WAGES/2,2)) PROMOTION_WAGES_NEW,
MAX(ROUND(A.PROMOTION_WAGES/2,2)) JIABAN_WAGES,
MAX(A.GROSS) GROSS,
MAX(A.DEDUCE) DEDUCE,
MAX(A.NET) NET,
MAX(DECODE(B.AD,'D001',AD_AMT,0)) POSITION,
MAX(DECODE(B.AD,'D011',AD_AMT,0)) MANGUN,
MAX(DECODE(B.AD,'D101',AD_AMT,0)) JAGYUK,
MAX(DECODE(B.AD,'D105',AD_AMT,0)) G3TECH,
MAX(DECODE(B.AD,'D107',AD_AMT,0)) G3HOURSE,
MAX(DECODE(B.AD,'D103',AD_AMT,0)) AWARD,
MAX(DECODE(B.AD,'D002',AD_AMT,0)) BOKLI,
MAX(DECODE(B.AD,'D003',AD_AMT,0)) GYEBAL,
MAX(DECODE(B.AD,'D004',AD_AMT,0)) SEASON_SUMMER,
MAX(DECODE(B.AD,'D005',AD_AMT,0)) SEASON_WINTER,
MAX(DECODE(B.AD,'D009',AD_AMT,0))
+ MAX(DECODE(B.AD,'D010',AD_AMT,0)) DANGJIK,
MAX(DECODE(B.AD,'D006',AD_AMT,0)) OT1,
MAX(DECODE(B.AD,'D007',AD_AMT,0)) OT2,
MAX(DECODE(B.AD,'D008',AD_AMT,0)) OT3,
MAX(DECODE(B.AD,'D100',AD_AMT,0)) SOGUB_PLUS,
MAX(DECODE(B.AD,'D020',AD_AMT,0)) YABAN,
MAX(DECODE(B.AD,'C166',AD_AMT,0)) SIKDEA,
MAX(DECODE(B.AD,'C164',AD_AMT,0)) KISUKSA,
MAX(DECODE(B.AD,'C165',AD_AMT,0)) WATER_TAX,
MAX(DECODE(B.AD,'C163',AD_AMT,0)) ELECTRIC_TAX,
MAX(DECODE(B.AD,'C201',AD_AMT,0)) YANGNO_INSURANCE,
MAX(DECODE(B.AD,'C202',AD_AMT,0)) EUELYO_INSURANCE,
MAX(DECODE(B.AD,'C203',AD_AMT,0)) JUBANG_INSURANCE,
MAX(DECODE(B.AD,'C204',AD_AMT,0)) GONGSANG_INSURANCE,
MAX(DECODE(B.AD,'C205',AD_AMT,0)) SILUP_INSURANCE,
MAX(DECODE(B.AD,'C206',AD_AMT,0)) SEANGYUK_INSURANCE,
MAX(DECODE(B.AD,'C201',EMPLOYER_AMT,0)) YANGNO_INSURANCE_C,
MAX(DECODE(B.AD,'C202',EMPLOYER_AMT,0)) EUELYO_INSURANCE_C,
MAX(DECODE(B.AD,'C203',EMPLOYER_AMT,0)) JUBANG_INSURANCE_C,
MAX(DECODE(B.AD,'C204',EMPLOYER_AMT,0)) GONGSANG_INSURANCE_C,
MAX(DECODE(B.AD,'C205',EMPLOYER_AMT,0)) SILUP_INSURANCE_C,
MAX(DECODE(B.AD,'C206',EMPLOYER_AMT,0)) SEANGYUK_INSURANCE_C,
MAX(DECODE(B.AD,'C207',AD_AMT,0)) SODUK_TAX,
MAX(DECODE(B.AD,'C100',AD_AMT,0)) SOGUB_MINUS,
MAX(A.DEPT) DEPT,
MAX(A.COSTCT) COSTCT,
MAX(A.JG) JG,
MAX(A.JL) JL,
MAX(A.NAME) NAME,
MAX(A.NET_BASIC) NET_BASIC,
MAX(A.NET_PI) NET_PI,
MAX(A.GITA_SUDANG1) GITA_SUDANG1,
MAX(DECODE(B.AD,'D040',AD_AMT,0)) OVER_PAY,
MAX(DECODE(B.AD,'D051',AD_AMT,0)) ADD_PAY,
MAX(DECODE(B.AD,'D109',AD_AMT,0)) ALLOWANCE_OTHER,
MAX(DECODE(B.AD,'C133',AD_AMT,0)) MULCT,
MAX(DECODE(B.AD,'D110',AD_AMT,0)) GONGLING
FROM PAYHIS1T A, PAYHIS2T B
WHERE A.EMP_ID = B.EMP_ID (+)
AND A.EFFECTIVE_MONTH = B.EFFECTIVE_MONTH (+)
GROUP BY A.EFFECTIVE_MONTH,
A.EMP_ID
lonesashimi，你说的拼成的查询是不是说用select替换所有的decode,然后放到from后面
oracle好像不支持欠套查询
扫一扫
556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
