这是Ross Bush回答的简化版本(它删除了CTE的联接，使事情更加集中，加快工作速度并减少了工作量.代码).

Here's a simplified version of Ross Bush's answer (It removes a join from the CTE to keep things more focussed, speed things up, and cut down the code).

;WITH
  ordered_ics_transactions AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY requisitionnumber
                           ORDER BY originaldate DESC
                      )
                        AS seq_id
  FROM
    dbo.ics_transactions
)
SELECT
  s.supplies_id,      s.old_itemid,
  s.itemdescription,  s.onhand,
  s.reorderlevel,     s.reorderamt,
  s.unitmeasure,      s.supplylocation,
  s.invtype,          s.discontinued,
  s.supply,
  t.requsitionnumber, t.openclosed,
  t.transtype,        t.originaldate
FROM
  dbo.ics_supplies           AS s
LEFT OUTER JOIN
  dbo.ics_orders             AS o
    ON  o.supplies_id = s.suppliesid
LEFT OUTER JOIN
  ordered_ics_transactions   AS t
    ON  t.requisitionnumber = o.requisitionnumber
    AND t.transtype         = 'PO'
    AND t.seq_id            = 1

这仅会结合每个请购单编号的最新交易记录，并且只有它具有 transtype ='PO'

This will only join the most recent transaction record for each requisitionnumber, and only if it has transtype = 'PO'

如果您要撤消该(仅合并具有 transtype ='PO'的交易记录，而仅合并最近的交易记录)，然后移动 transtype ='PO'过滤为 ordered_ics_transactions CTE中的 WHERE 子句.

IF you want to reverse that (joining only transaction records that have transtype = 'PO', and of those only the most recent one), then move the transtype = 'PO' filter to be a WHERE clause inside the ordered_ics_transactions CTE.