这句ｓｑｌ还能再优化吗

Question

这句ｓｑｌ还能再优化吗    
UPDATE
          CM_SKK2 A
    SET S14 =
         (SELECT
            DECODE (WK_CM_NO,
            '00', SUM(KARI_KIN_00) - SUM(KASI_KIN_00),
            '01', SUM(KARI_KIN_01) - SUM(KASI_KIN_01),
            '02', SUM(KARI_KIN_02) - SUM(KASI_KIN_02),
            '03', SUM(KARI_KIN_03) - SUM(KASI_KIN_03),
            '04', SUM(KARI_KIN_04) - SUM(KASI_KIN_04),
            '05', SUM(KARI_KIN_05) - SUM(KASI_KIN_05),
            '06', SUM(KARI_KIN_06) - SUM(KASI_KIN_06),
            '07', SUM(KARI_KIN_07) - SUM(KASI_KIN_07),
            '08', SUM(KARI_KIN_08) - SUM(KASI_KIN_08),
            '09', SUM(KARI_KIN_09) - SUM(KASI_KIN_09),
            '10', SUM(KARI_KIN_10) - SUM(KASI_KIN_10),
            '11', SUM(KARI_KIN_11) - SUM(KASI_KIN_11),
            '12', SUM(KARI_KIN_12) - SUM(KASI_KIN_12),
            '13', SUM(KARI_KIN_13) - SUM(KASI_KIN_13),
            '14', SUM(KARI_KIN_14) - SUM(KASI_KIN_14),
            DEF_CURRENCY)
        FROM
            CBMSCA.CATZAN2R B
        WHERE
            B.SEG1_CD = A.S01
            AND B.ACCT_YEAR = WK_CM_NEN
            AND B.ACCT_CD = '751A121'
            AND B.ASSI_CD = '0000000000'
            AND B.DEP_CD  = '000'
        GROUP BY
            B.SEG1_CD
         )
    WHERE
        A.S01 =
        (SELECT 
            B.SEG1_CD
        FROM
            CBMSCA.CATZAN2R B
        WHERE
            B.SEG1_CD = A.S01
            AND B.ACCT_YEAR = WK_CM_NEN
            AND B.ACCT_CD = '751A121'
            AND B.ASSI_CD = '0000000000'
            AND B.DEP_CD  = '000'
        GROUP BY
            B.SEG1_CD
         );

Answer 1

SUM(KARI_KIN_00) - SUM(KASI_KIN_00) ---这个有意义吗,两个相同的值相减

Answer 2

-- JUST TRY IT ..
UPDATE CM_SKK2 A
   SET S14 = (SELECT DECODE(WK_CM_NO,
                            '00',
                            SUM(KARI_KIN_00) - SUM(KASI_KIN_00),
                            '01',
                            SUM(KARI_KIN_01) - SUM(KASI_KIN_01),
                            '02',
                            SUM(KARI_KIN_02) - SUM(KASI_KIN_02),
                            '03',
                            SUM(KARI_KIN_03) - SUM(KASI_KIN_03),
                            '04',
                            SUM(KARI_KIN_04) - SUM(KASI_KIN_04),
                            '05',
                            SUM(KARI_KIN_05) - SUM(KASI_KIN_05),
                            '06',
                            SUM(KARI_KIN_06) - SUM(KASI_KIN_06),
                            '07',
                            SUM(KARI_KIN_07) - SUM(KASI_KIN_07),
                            '08',
                            SUM(KARI_KIN_08) - SUM(KASI_KIN_08),
                            '09',
                            SUM(KARI_KIN_09) - SUM(KASI_KIN_09),
                            '10',
                            SUM(KARI_KIN_10) - SUM(KASI_KIN_10),
                            '11',
                            SUM(KARI_KIN_11) - SUM(KASI_KIN_11),
                            '12',
                            SUM(KARI_KIN_12) - SUM(KASI_KIN_12),
                            '13',
                            SUM(KARI_KIN_13) - SUM(KASI_KIN_13),
                            '14',
                            SUM(KARI_KIN_14) - SUM(KASI_KIN_14),
                            DEF_CURRENCY)
                FROM CBMSCA.CATZAN2R B
               WHERE B.SEG1_CD = A.S01
                 AND B.ACCT_YEAR = WK_CM_NEN
                 AND B.ACCT_CD = '751A121'
                 AND B.ASSI_CD = '0000000000'
                 AND B.DEP_CD = '000'
               GROUP BY B.SEG1_CD)
 WHERE EXISTS (SELECT 1
          FROM CBMSCA.CATZAN2R B
         WHERE B.SEG1_CD = A.S01
           AND B.ACCT_YEAR = WK_CM_NEN
           AND B.ACCT_CD = '751A121'
           AND B.ASSI_CD = '0000000000'
           AND B.DEP_CD = '000');

Answer 3

SUM(KARI_KIN_14) - SUM(KASI_KIN_14),可以改成SUM(KARI_KIN_14 - KASI_KIN_14),

Answer 4

/--试一下：UPDATE CM_SKK2 A, CBMSCA.CATZAN2R B
   SET S14   = DECODE(B.WK_CM_NO,
                      '00',
                      SUM(KARI_KIN_00) - SUM(KASI_KIN_00),
                      '01',
                      SUM(KARI_KIN_01) - SUM(KASI_KIN_01),
                      '02',
                      SUM(KARI_KIN_02) - SUM(KASI_KIN_02),
                      '03',
                      SUM(KARI_KIN_03) - SUM(KASI_KIN_03),
                      '04',
                      SUM(KARI_KIN_04) - SUM(KASI_KIN_04),
                      '05',
                      SUM(KARI_KIN_05) - SUM(KASI_KIN_05),
                      '06',
                      SUM(KARI_KIN_06) - SUM(KASI_KIN_06),
                      '07',
                      SUM(KARI_KIN_07) - SUM(KASI_KIN_07),
                      '08',
                      SUM(KARI_KIN_08) - SUM(KASI_KIN_08),
                      '09',
                      SUM(KARI_KIN_09) - SUM(KASI_KIN_09),
                      '10',
                      SUM(KARI_KIN_10) - SUM(KASI_KIN_10),
                      '11',
                      SUM(KARI_KIN_11) - SUM(KASI_KIN_11),
                      '12',
                      SUM(KARI_KIN_12) - SUM(KASI_KIN_12),
                      '13',
                      SUM(KARI_KIN_13) - SUM(KASI_KIN_13),
                      '14',
                      SUM(KARI_KIN_14) - SUM(KASI_KIN_14),
                      DEF_CURRENCY),
       A.S01 = B.SEG1_CD
 WHERE B.SEG1_CD = A.S01
   AND B.ACCT_YEAR = WK_CM_NEN
   AND B.ACCT_CD = '751A121'
   AND B.ASSI_CD = '0000000000'
   AND B.DEP_CD = '000' GROUP BY B.SEG1_CD;

Answer 5

把你的第一个group by去掉，
第二个如果没重复数据，也可去掉。

Answer 6

执行了下 可是提示set语句出错，还有好像没有SELECT 就不能用GROUP BY 了吧