排除除一个不同字段以外的重复记录

Question

我有包含客户编号，日期，金额和交易类型（买入或卖出）的交易列表。我只想返回未被相同客户，日期和金额取消但具有相反交易类型的交易（每位客户，每天，每笔金额）。

例如，我的示例表（交易）低于：

 
+----------+------------+--------+--------+ 
| Customer | Date | Amount | Action | 
+----------+------------+--------+--------+ 
|  1111 | 15/09/2016 |  5 | Buy | 
|  2222 | 15/09/2016 |  5 | Sell | 
|  3333 | 15/09/2016 |  6 | Buy | 
|  3333 | 15/09/2016 |  6 | Sell | 
|  4444 | 15/09/2016 |  7 | Sell | 
|  2222 | 15/09/2016 |  6 | Buy | 
+----------+------------+--------+--------+ 

我只想以下项目返回：

 
+----------+------------+--------+--------+ 
| Customer | Date | Amount | Action | 
+----------+------------+--------+--------+ 
|  1111 | 15/09/2016 |  5 | Buy | 
|  2222 | 15/09/2016 |  5 | Sell | 
|  4444 | 15/09/2016 |  7 | Sell | 
|  2222 | 15/09/2016 |  6 | Buy | 
+----------+------------+--------+--------+ 

客户3333已经被排除在外，因为他们有一个买入和在同一天出售相同价值的金额。客户2222已被留下，尽管他们在同一天进行了买卖，这是针对不同的金额，所以我需要为他们返回两个记录。

Answer 1

这将是不够的：

SELECT Customer, Date, Amount, MAX(Action) Action 
FROM Src 
GROUP BY Customer, Date, Amount 
HAVING COUNT(*)=1 

测试用例

WITH Src AS 
(
    SELECT * FROM (VALUES 
    (1111, '15/09/2016', 5, 'Buy'), 
    (2222, '15/09/2016', 5, 'Sell'), 
    (3333, '15/09/2016', 6, 'Buy'), 
    (3333, '15/09/2016', 6, 'Sell'), 
    (4444, '15/09/2016', 7, 'Sell'), 
    (2222, '15/09/2016', 6, 'Buy') 
    ) T(Customer, Date, Amount, Action) 
) 
SELECT Customer, Date, Amount, MAX(Action) Action 
FROM Src 
GROUP BY Customer, Date, Amount 
HAVING COUNT(*)=1 

结果

Customer Date  Amount  Action 
----------- ---------- ----------- ---- 
1111  15/09/2016 5   Buy 
2222  15/09/2016 5   Sell 
2222  15/09/2016 6   Buy 
4444  15/09/2016 7   Sell