如何关联SQL表，返回同时满足两类支付条件的交易编号？

现有表结构 #

• pos.transaction 表：
  • Id（主键）
  • MainframeTransactionSequenceNumber（字符串类型）
  • TransactionNumber（字符串类型）
• pos.payment 表：
  • Id（主键）
  • TransactionId（外键，关联pos.transaction.Id）
  • TenderSubTypeId（整数类型）
  • AmountBaseCurrency（小数类型）

查询需求 #

找出所有满足以下条件的TransactionNumber：

• 对应交易至少包含两笔支付
• 其中一笔支付的TenderSubTypeId = 31，且该笔支付的AmountBaseCurrency < 0
• 另一笔支付的TenderSubTypeId = 37，且该笔支付的AmountBaseCurrency < 0

现有查询问题 #

当前编写的查询只能返回满足TenderSubTypeId=31或TenderSubTypeId=37任一条件的数据，无法确保交易同时包含两类符合要求的支付。现有查询代码：

select * from pos.[transaction] t
left join pos.[payment] p on t.id = p.transactionid
where (p.TenderSubTypeId = 31 or p.TenderSubTypeId = 37)
and t.AmountTotalBaseCurrency < 0

要实现需求，你可以用分组+条件聚合的方式筛选符合要求的交易，再关联交易表获取TransactionNumber，具体SQL如下：

select distinct t.TransactionNumber
from pos.[transaction] t
inner join pos.[payment] p on t.Id = p.TransactionId
where p.TenderSubTypeId in (31, 37)
  and p.AmountBaseCurrency < 0
group by t.Id, t.TransactionNumber
having count(distinct p.TenderSubTypeId) = 2

逻辑说明 #

1. 先通过where子句筛选出TenderSubTypeId为31或37、且金额小于0的支付记录，提前排除无效数据。
2. 按交易的Id和TransactionNumber分组，保证每个组对应唯一的一笔交易。
3. 用having count(distinct p.TenderSubTypeId) = 2验证：只有当交易同时包含31和37两类符合条件的支付时，去重后的计数才会等于2，以此确保双条件同时满足。
4. 加distinct是为了避免返回重复的TransactionNumber（分组后理论上不会重复，但加上更稳妥）。

如果觉得聚合函数不好理解，也可以用两次关联支付表的方式，逻辑更直白：

select distinct t.TransactionNumber
from pos.[transaction] t
inner join pos.[payment] p1 on t.Id = p1.TransactionId
inner join pos.[payment] p2 on t.Id = p2.TransactionId
where p1.TenderSubTypeId = 31 and p1.AmountBaseCurrency < 0
  and p2.TenderSubTypeId = 37 and p2.AmountBaseCurrency < 0

方案对比 #

• 聚合方式性能更优，数据量大时优势明显，因为先过滤再分组，计算成本更低。
• 两次关联方式逻辑简单易懂，适合小数据量或对聚合函数不熟悉的场景。

内容的提问来源于stack exchange，提问作者Bhav