这个SQL写了很久的时间，感觉pgSQL的很是麻烦。

with as 先命名一个表出来，就可以当成临时表用。

WITH tmp AS (    SELECT        MAX(mgi.inner_cd) AS innerCd,        mgi.ginner_code AS ginner_code,        mgi.subsidiary_code AS subsidiary_code    FROM        prod.m_ginner_inner11 mgi,        TEMP .hscode_renkei_manage aa    WHERE        mgi.subsidiary_code = aa.subsidiary_cd    AND aa.ginner_code = mgi.GINNER_CODE    GROUP BY        mgi.subsidiary_code,        mgi.ginner_code) UPDATE TEMP.hscode_renkei_manage htmSET inner_cd =  tmp.innerCdFROMtmpWHERE    htm.ginner_code  =  tmp.ginner_codeAND htm.subsidiary_cd = tmp.subsidiary_code

主要是要注意命名，稍不注意，就报错。

在使用using和删除一起用。

WITH price_inner AS    (        SELECT            MP.AA,            MP.BB,            MP.CC        FROM            ${temp}.ss MP,                                                            ${temp}.qqq MKI                                                   WHERE            MP.a = #{p1,jdbcType=VARCHAR}        AND MP.b = #{p2,jdbcType=VARCHAR}         AND MP.c = #{p3,jdbcType=VARCHAR}              )      DELETE FROM             ${temp}.${tableName} MUP      USING  price_inner      WHERE           MUP.a = price_inner.AA       AND MUP.b = price_inner.BB       AND MUP.c = price_inner.CC

 和insert一起用。

WITH tmp_product AS (    SELECT DISTINCT    aa,    bb,    FROM         hrm            WHERE        hrm.a = ? AND        hrm.b = ?        ) INSERT INTO ${temp}.m_product (        aa,        bb    ) SELECT        tmp_product.aa,        tmp_product.bb    FROM        tmp_product