我有一张桌子,上面写有交易数据。我需要在加入其他表时查询上述交易,但是d.originalorderid不是唯一条目,并且我得到了几个重复条目。我想要:


为每个唯一的d.originalorderid选择一行
该行应该是最新的(最大ID)


我将如何处理?这是我现在有的查询。

SELECT  d.id,
        d.date,
        d.ip, d.panmask,
        d.merchantorderid,
        d.amount,
        d.cardholder,
        d.bankhumanname,
        d.cardtypeid,
        d.bankcountrycode,
        d.usercountrycode,
        mc.paymentkey as merchantname,
        dt.status,
        d.merchantcontract,
        dt.tag,
        d.originalorderid,
        ds.refnumber,
        ds.dealauthcode,
        mc.processingid,
        pc.Name as processing,
        d.customparams
FROM Deal as d
LEFT JOIN MerchantContract as mc ON mc.Id = d.MerchantContract
LEFT JOIN DealTrace as dt ON d.Id = dt.DealId
   AND dt.id = (SELECT MAX(id)
                FROM DealTrace WITH (nolock)
                WHERE DealId = d.id)
LEFT JOIN DealSummary ds ON d.Id = ds.DealId
   AND ds.id = (SELECT MAX(id)
                FROM DealSummary WITH (nolock)
                WHERE DealId = d.id)
LEFT JOIN Processing pc on mc.ProcessingId = pc.id
WHERE (d.MerchantContract IN ('12'))
ORDER BY ID desc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

最佳答案

如果我理解需求,则不必加入交易表,而要加入到它上面的相关子查询,该查询返回具有最高交易ID且具有相同原始订单ID的交易。测试一下,但我认为还可以。

SELECT ....
FROM
(SELECT *
FROM Deal d1
WHERE d1.Id=(SELECT MAX(Id)
                         FROM Deal d2
                         WHERE d2.OriginalOrderId=d1.OriginalOrderId)) d
LEFT JOIN MerchantContract as mc ON mc.Id = d.MerchantContract
 etc etc

08-04 17:20