将行分组在一起以查看交易记录是否包含退款

Question

(TransactionID, UserID, Type, OrderNumber, Amount)
1, 123, Purchase, 100, 100
2, 123, Refund, 100, 100
3, 234, Purchase, 101, 100
4, 345, Purchase, 102, 100

我想创建一个将返回所有事务的查询，这些事务按当前没有返回给定OrderNumber的userID分组。

我需要为每个用户进行退款交易，如果已经存在，我不想退款。

是否可以进行订购和分组，以便我可以找到这些交易？

Answer 1

您可以在这里使用存在逻辑：

SELECT t1.*
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2
WHERE t2.UserID = t1.UserID AND t2.Type = 'Refund');

上面查询的逻辑读起来只是返回给定用户的所有交易记录，而我们找不到属于退款的同一用户的单个记录。

Answer 2

您也可以使用条件聚合对这样的记录进行分组。

create table Transactions (TransactionID int, UserID int, [Type] varchar(20)
, OrderNumber int, Amount int)
insert into Transactions values
(1, 123, 'Purchase', 100, 100),
(2, 123, 'Refund', 100, 100),
(3, 234, 'Purchase', 101, 100),
(4, 345, 'Purchase', 102, 100)
Select UserId
, max(CASE WHEN  [Type] ='Purchase' then Amount ELSE 0 END) as PurchaseAmount
, max(CASE WHEN  [Type] ='Refund' then Amount ELSE 0 END) as RefundAmount
from Transactions
group by UserID

Live数据库<>小提琴演示。

Answer 3

根据@Suraj建议的表格，以下SQL应按用户向您提供退款金额

select pur.UserID, 
RefundAmount = sum(pur.Amount - Isnull(refund.Amount, 0))
from Transactions pur
left join Transactions refund 
on pur.UserID = refund.UserID 
and pur.OrderNumber = refund.OrderNumber
and refund.Type = 'Refund'
where pur.Type = 'Purchase'
group by pur.UserID