SQL-需要查找重复记录，但排除反向事务 [英] SQL - Need to find duplicate records but EXCLUDE reversed transactions
问题描述
我有一个交易表，偶尔会有
个重复条目。如果/当管理员找到这些重复项时，它们将撤消交易，因此产生负值（但由于法规要求，原始重复项仍然保留）。我想创建一个SQL查询（并使用Crystal Reports）为管理员提供报告，以方便地查找重复的交易。由于交易数量众多，我想通过忽略已撤消的交易来使他们更轻松。
I have a table of transactions which will occasionally have duplicate entries. If/When an admin finds these duplicate entries, they will reverse the transactions, therefore creating a negative value (but the original duplicate still remains due to regulatory requirements). I'd like to create a SQL query (and use Crystal Reports) to make a report for the admins to easily find duplicate transactions. Due to the mass quantity of transactions, I'd like to make it easier for them by disregarding transactions that they have already reversed.
以下是我想要的示例愿意这样做：
Here's an example of what I'd like to do:
交易日期；交易数量;交易价值;反转
1/1/08 ; 14 ; 70.00 ; N
1/1/08 ; 14 ; 70.00 ; N
1/1/08 ; -14 ; -70.00 ; Y
2/1/08 ; 17 ; 89.00 ; N
2/15/08 ; 18 ; 95.00 ; N
2/15/08 ; 18 ; 95.00 ; N
3/1/08 ; 11 ; 54.00 ; N
3/1/08 ; -11 ; -54.00 ; Y
3/1/08 ; 11 ; 54.00 ; N
3/1/08 ; 11 ; 54.00 ; N
3/1/08 ; 11 ; 54.00 ; N
理想情况下，如果我在上表中运行所需查询，我将获得
收到以下结果：
Ideally, if I ran my "desired" query on the table above, I would receive the following result:
交易日期；交易数量;交易价值;计数
2/15/08 ; 18 ; 95.00 ; 2
3/1/08 ; 11 ; 54.00 ; 3
这有意义吗？我已经想出了如何编写
查询来为我提供重复计数的方法，但是我无法弄清楚
如何排除已经回退的重复记录。
任何帮助将不胜感激！
Does that make sense? I've already figured out how to write the query to give me a count of duplicates, but I can't figure out how to exclude duplicate records which have been "backed out" already. Any help would be greatly appreciated!
推荐答案
如何：
select dt, abs(qty), abs(val),
sum(case when reversal='Y' then -1 else 1 end) as count
from transactions
group by dt, abs(qty), abs(val)
having sum(case when reversal='Y' then -1 else 1 end) > 1;
我刚刚在Oracle中对其进行了测试，并且可以正常工作：
I have just tested it in Oracle and it works:
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 2
01-MAR-2008 11 54 3
这篇关于SQL-需要查找重复记录，但排除反向事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
