SQL 在没有子查询的情况下获取最新结果

Question

我正在使用以下查询获取某些项目信息:

select inventory.itemnum, item.description, invcost.avgcost, invtrans.physcnt
from inventory
    join item on inventory.itemnum = item.itemnum
    join invcost on inventory.itemnum = invcost.itemnum
    join invtrans on inventory.itemnum = invtrans.itemnum
where inventory.location = 'A'
    and invcost.location = 'A' and invtrans.transdate like '%2015%'
    and invtrans.transtype = 'PCOUNTADJ'
    and invtrans.storeloc = 'A';

查询当前只为每个项目返回一条记录，它使用基于当前年份(例如 2015 年)的 INVTRANS 表中的第一条记录。当那一年有多个类型为“PCOUNTADJ”的 INVTRANS 记录时，这就成了一个问题。

我怎样才能只使用最新的 (max(transdate)) 而不是？

我知道我可以像这样使用子查询:

...invtrans.transdate = (select max(transdate) from INVTRANS where...)

但是我将不得不重复与 INVENTORY 的连接，并且我丢失了对外部查询的 itemnum 的引用

Answer 1

您可以使用CROSS APPLY:

select inventory.itemnum, item.description, invcost.avgcost, i.physcnt
from inventory 
join item on inventory.itemnum = item.itemnum 
join invcost on inventory.itemnum = invcost.itemnum 
cross apply (
   select top 1 physcnt
   from invtrans
   where inventory.itemnum = invtrans.itemnum and 
         invtrans.transdate like '%2015%' and
         invtrans.transtype = 'PCOUNTADJ' and 
         invtrans.storeloc = 'A'
   order by transdate desc) AS i(physcnt)    
where inventory.location = 'A' and invcost.location = 'A';

如果 2015 有多个类型为 'PCOUNTADJ' 的 INVTRANS 记录，CROSS APPLY 返回一个有最新的transdate。

另一种方法涉及窗口函数:

select itemnum, description, avgcost, physcnt
from (
  select inventory.itemnum, item.description, 
         invcost.avgcost, invtrans.physcnt,
         row_number() over (partition by invtrans.itemnum 
                            order by invtrans.transdate desc) as rn
  from inventory 
  join item on inventory.itemnum = item.itemnum 
  join invcost on inventory.itemnum = invcost.itemnum 
  join invtrans on inventory.itemnum = invtrans.itemnum
  where inventory.location = 'A' and invcost.location = 'A' and 
        invtrans.transdate like '%2015%' and 
        invtrans.transtype = 'PCOUNTADJ' and 
        invtrans.storeloc = 'A' ) as t
where t.rn = 1 

上述查询中的

ROW_NUMBER 为每个 itemnum 的最新 行返回 1。