子查询SQL

--1、如果明细合计等于明细已对账合计则主单状态为已对账
--2、如果明细合计等于明细未对账合计则主单状态为未对账
--3、其它情况为对账中
UPDATE E_F_THIRDPTSTMT A
   SET A.TPS_STATE =
       (SELECT (CASE
                 WHEN (CC.B - CC.C) = 0 THEN
                  '3' ---已对账
                 WHEN (CC.D - CC.B) = 0 THEN
                  '1' --未对账
                 ELSE
                  '2' --对账中
               END)
          FROM (SELECT B.TPS_ID,
                       COUNT(1) AS "B", --合计
                       SUM(DECODE(B.TPSI_STATES, 3, 1, 0)) AS "C", --已对账
                       SUM(DECODE(B.TPSI_STATES, 1, 1, 0)) AS "D" --未对账
                  FROM E_F_THIRDPTSTMT_ITEM B
                  GROUP BY B.TPS_ID
                 ) CC WHERE CC.TPS_ID = A.ID);