具有复合主键的表中具有MAX（DATE）的GROUP BY(GROUP BY with MAX(DATE) in table with composite primary key)

具有复合主键的表中具有MAX（DATE）的GROUP BY(GROUP BY with MAX(DATE) in table with composite primary key)

我正在尝试列出表中每个SKU的BarCode，但BarCode在某些情况下是不同的，所以我想从一个sku中获取一个条形码，返回的条形码应该是最近更新的条形码。

例：

StoreID SKU BarCode UpdateDate ------------------------------------------------------------- 1 95810 28471000000 10/06/2016 04:20:00 a.m. 1 95810 30040050033 01/03/2012 01:00:00 a.m. 2 44320 65453102001 15/05/2010 01:00:00 a.m. 2 44320 12343102001 01/01/2015 01:00:00 a.m.

期望的结果应该是：

StoreID SKU BarCode UpdateDate ------------------------------------------------------------- 1 95810 28471000000 10/06/2016 04:20:00 a.m. 2 44320 12343102001 01/01/2015 01:00:00 a.m.

我试过用

SELECT t.SKU, r.MaxTime FROM (SELECT P.SKU, MAX(P.Fec_Movto) as MaxTime FROM Productos as P GROUP BY P.SKU) r INNER JOIN Productos t ON t.SKU = r.SKU AND t.Fec_Movto = r.MaxTime

但问题是主键是复合主键，因此它根本不起作用。

I'm trying to list the BarCode for each SKU in a table but the BarCode is different in some cases, so i want to get from one sku one barcode and the barcode returned should be the one with the UpdateDate most recent.

Example:

StoreID SKU BarCode UpdateDate ------------------------------------------------------------- 1 95810 28471000000 10/06/2016 04:20:00 a.m. 1 95810 30040050033 01/03/2012 01:00:00 a.m. 2 44320 65453102001 15/05/2010 01:00:00 a.m. 2 44320 12343102001 01/01/2015 01:00:00 a.m.

The desired result should be:

StoreID SKU BarCode UpdateDate ------------------------------------------------------------- 1 95810 28471000000 10/06/2016 04:20:00 a.m. 2 44320 12343102001 01/01/2015 01:00:00 a.m.

I have tried using

SELECT t.SKU, r.MaxTime FROM (SELECT P.SKU, MAX(P.Fec_Movto) as MaxTime FROM Productos as P GROUP BY P.SKU) r INNER JOIN Productos t ON t.SKU = r.SKU AND t.Fec_Movto = r.MaxTime

but the problem here is that primary key is a composite primary key so it doesn't work at all.

最满意答案

可能会使用row_number之类的

select * from ( select StoreID,SKU,BarCode,UpdateDate ,row_number() over ( partition by SKU order by UpdateDate desc) rowid from Productos ) t where rowid = 1

may be use row_number like

select * from ( select StoreID,SKU,BarCode,UpdateDate ,row_number() over ( partition by SKU order by UpdateDate desc) rowid from Productos ) t where rowid = 1