一条sql搞定从查询语句中向目标表中插入记录并去重发插入

最近做一个功能，需要从一个试图中查询出相关数据，并将数据中的某些数据插入到另一张表中，但每次操作都会去匹配是否有合适的数据，并将合适的数据插入到数据表中，故不能插入重复数据记录，现贴出我个人的作法

写一条sql搞定，只是每次操作都会执行此sql

merge into TNW_PAY_CONFIRM m using (select * from(      select b.TO_ACCOUNT_NAME,a.SUBSCRIPTION_ACCOUNT,a.SUBSCRIPTION_MONEY,a.SUBSCRIPTION_TIME,a.NAME,a.PRODUCT_ID,b.TRADE_TIME,a.ID,row_number() over(partition by to_account_no,b.TRADE_TIME order by SUBSCRIPTION_time desc ) rn,      case   when (a.SUBSCRIPTION_MONEY is not null and b.CREDIT_MONEY is not null and a.SUBSCRIPTION_MONEY != b.CREDIT_MONEY)   then '不匹配'   when (a.SUBSCRIPTION_NAME is null and b.TO_ACCOUNT_NAME is not null)   then '不匹配'   when (b.CREDIT_MONEY is null)   then '不匹配'   when (a.SUBSCRIPTION_MONEY=b.CREDIT_MONEY and a.SUBSCRIPTION_ACCOUNT is not null and b.TO_ACCOUNT_NO is not null and a.SUBSCRIPTION_ACCOUNT != b.TO_ACCOUNT_NO)   then '不匹配'   when (a.SUBSCRIPTION_NAME is NOT null AND a.SUBSCRIPTION_NAME=b.TO_ACCOUNT_NAME AND a.SUBSCRIPTION_MONEY is not null AND a.SUBSCRIPTION_MONEY=b.CREDIT_MONEY AND b.TO_ACCOUNT_NO is not null AND a.SUBSCRIPTION_ACCOUNT = b.TO_ACCOUNT_NO)   then '匹配'   end isMatch,   case   when (a.SUBSCRIPTION_NAME is null and b.TO_ACCOUNT_NAME is not null)   then '未提供数据(无认购用户)'   when a.SUBSCRIPTION_ACCOUNT != b.TO_ACCOUNT_NO   then '账号不符:到账账号为:'||b.TO_ACCOUNT_NO   when (a.SUBSCRIPTION_MONEY is not null and b.CREDIT_MONEY is not null and a.SUBSCRIPTION_MONEY != b.CREDIT_MONEY)    then '金额不符'   when (a.SUBSCRIPTION_NAME is not null and b.TO_ACCOUNT_NAME is not null and b.CREDIT_MONEY is null)   then '未到账'   when (a.SUBSCRIPTION_NAME is NOT null AND a.SUBSCRIPTION_NAME=b.TO_ACCOUNT_NAME AND a.SUBSCRIPTION_MONEY is not null AND a.SUBSCRIPTION_MONEY=b.CREDIT_MONEY AND b.TO_ACCOUNT_NO is not null AND a.SUBSCRIPTION_ACCOUNT = b.TO_ACCOUNT_NO)   then '已到账'   end result        from TNW_SUBSCRIPTION a         right join       (select TO_ACCOUNT_NAME,CREDIT_MONEY,to_char(TRADE_TIME, 'yyyy-mm-dd hh24:mi:ss') trade_time,TO_ACCOUNT_NO from (     select t.TO_ACCOUNT_NAME,sum(t.CREDIT_MONEY) credit_money,           trunc(to_date(t.TRADE_TIME,'yyyy-mm-dd hh24:mi:ss')) trade_time,t.TO_ACCOUNT_NO from (                 select a.TO_ACCOUNT_NAME,a.TO_ACCOUNT_NO,to_char(a.TRADE_TIME, 'yyyy-mm-dd hh24:mi:ss') trade_time,a.CREDIT_MONEY,a.REFERENCE from                 ts_bank_account_flow_view a JOIN ts_bank_account_info_view b ON a.ACCOUNTID = b.ID WHERE b.account_no = '321060100100154273'  AND a.TRADE_TYPE = '1'                 AND a.REFERENCE NOT LIKE '%手续%' AND a.REFERENCE NOT LIKE '%结息%' ORDER BY to_account_no) t                   GROUP BY t.TO_ACCOUNT_NAME,trunc(to_date(t.TRADE_TIME,'yyyy-mm-dd hh24:mi:ss')),t.TO_ACCOUNT_NO)) b     on a.SUBSCRIPTION_NAME = b.TO_ACCOUNT_NAME and a.RAISE_ACCOUNT = '321060100100154273'       )where ISMATCH='匹配'   ) n on (m.SUBSCRIBE_ID=n.ID)       when not matched then        insert (ID,SUBSCRIBE_ID,PRODUCT_ID,PAY_TIME,PAY_AMOUNT,REMARK, CREATE_TIME)    values       (28,n.ID,n.PRODUCT_ID,to_date(n.SUBSCRIPTION_TIME,'YYYY-MM-DD HH24:MI:SS'),n.SUBSCRIPTION_MONEY,'自动匹配',sysdate)  

sql比较长，此语句将需要的数据查询出来，然后根据on条件匹配目标表，如果目标表中没有此记录则将查询出来的数据添加到目标表中。