<p>这里有另一个观点可以补充到其他观点中。这只处理匹配部分,而不是更新或合并部分。最近我遇到了一个类似的问题,我需要查找与事务日期和位置匹配的记录,但是这些记录来自两个不同的来源。在这种情况下,必须已经对记录进行排序,以便将类似的记录放在一起。内部查询将记录与之前和之后的记录进行比较,如果它们匹配,则获取它。然后外部查询确定它们是否满足“差异”条件。希望这有帮助。在</p>
<pre><code>select sbs.trnsid, sbs.amount, sbs.transaction_date, sbs.posted_date, sbs.srcid,
sbs.credited_flag, sbs.accid, sbs.compid, sbs.badgeid, sbs.locid, sbs.date_credited,
sbs.searchable, sbs.priortime, sbs.nexttime, sbs.priorsource, sbs.nextsource
from
(select trnsid, amount, transaction_date, posted_date, srcid, credited_flag,
accid, compid, badgeid, locid, date_credited, transaction_date||locid as searchable,
lag(transaction_date||locid, 1) over (order by accid) as priortime,
lead(transaction_date||locid, 1) over (order by accid) as nexttime,
lag(srcid, 1) over (order by accid) as priorsource,
lead(srcid, 1) over (order by accid) as nextsource
from transactions_table
where accid = v_acct
and transaction_date >= to_date('10/01/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
and transaction_date <= to_date('04/23/2017 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
and srcid in ('B', 'S') order by accid, transaction_date, locid) sbs
where (sbs.searchable = sbs.nexttime and sbs.srcid = 'S' and sbs.nextsource = 'B')
or (sbs.searchable = sbs.priortime and sbs.srcid = 'B' and sbs.priorsource = 'S');
</code></pre>