SQL-需要查找重复记录，但排除反向事务

怎么样：

select dt, abs(qty), abs(val),       sum(case when reversal='Y' then -1 else 1 end) as countfrom transactionsgroup by dt, abs(qty), abs(val)having sum(case when reversal='Y' then -1 else 1 end) > 1;

我刚刚在Oracle中对其进行了测试，并且可以正常工作：

create table transactions( dt date, qty number, val number, reversal varchar2(1));insert into transactions values (to_date('1/1/08','mm/dd/yy')    , 14    ,    70.00    , 'N');insert into transactions values (to_date('1/1/08','mm/dd/yy')    , 14    ,    70.00    , 'N');insert into transactions values (to_date('1/1/08','mm/dd/yy')    , -14   ,    -70.00   , 'Y');insert into transactions values (to_date('2/1/08','mm/dd/yy')    , 17    ,    89.00    , 'N');insert into transactions values (to_date('2/15/08','mm/dd/yy')   , 18    ,    95.00    , 'N');insert into transactions values (to_date('2/15/08','mm/dd/yy')   , 18    ,    95.00    , 'N');insert into transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');insert into transactions values (to_date('3/1/08','mm/dd/yy')    , -11   ,    -54.00   , 'Y');insert into transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');insert into transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');insert into transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');SQL> select dt, abs(qty), abs(val),  2         sum(case when reversal='Y' then -1 else 1 end) as count  3  from transactions  4  group by dt, abs(qty), abs(val)  5  having sum(case when reversal='Y' then -1 else 1 end) > 1;DT ABS(QTY)   ABS(VAL)      COUNT----------- ---------- ---------- ----------15-FEB-2008         18         95          201-MAR-2008         11         54          3