具有复合主键的表中具有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.MaxTimebut 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 = 1may 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更多推荐
发布评论