关于ORACLE数据库UNION的用法实例

SELECT MIN(A1.PAYDATE) PAYDATE FROM (

  SELECT
    C.PAY_PRINCIPAL_DATE AS PAYDATE
  FROM
    ABS_PROJ_PRPS_INFO A,
    ABS_PROJ_SECS_INFO B,
    ABS_PROJ_PRIN_PLAN C
  WHERE
    A.PROPOSAL_ID = B.PROPOSAL_ID
  AND B.SECURITIES_ID = C.SECURITIES_ID
  AND A.ISSUED = 'Y'
  AND A.PROJECT_ID = #{projectId}

  UNION


  SELECT
    C.PAY_DATE AS PAYDATE
  FROM
    ABS_PROJ_PRPS_INFO A,
    ABS_PROJ_SECS_INFO B,
    ABS_PROJ_INTE_PLAN C
  WHERE
    A.PROPOSAL_ID = B.PROPOSAL_ID
  AND B.SECURITIES_ID = C.SECURITIES_ID
  AND A.ISSUED = 'Y'
  AND A.PROJECT_ID = #{projectId}

  UNION


  SELECT
    C.PAY_DATE AS PAYDATE
  FROM
    ABS_PROJ_PRPS_INFO A,
    ABS_PROJ_FEE_INFO B,
    ABS_PROJ_FEE_PLAN C
  WHERE
    A.PROPOSAL_ID = B.PROPOSAL_ID
  AND B.FEE_ID = C.FEE_ID
  AND A.ISSUED = 'Y'
  AND A.PROJECT_ID = #{projectId}
  ) A1 WHERE A1.PAYDATE > #{adjustDate}

UNION 的3张虚拟表的字段必须相同 即字段”PAYDATE” 会去重3张虚拟表 里字段值相同的数据组成一个不会有重复数据的结果集