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屋！
