获取每个项目的最新价格 [英] Get most recent Price for each Item

问题描述

我有一张桌子:

ItemID PurchaseDate Price  
001 03/17/2013 19.00  
002 03/17/2013 14.00  
001 03/18/2013 13.00  
002 03/18/2013 15.00  
001 03/19/2013 17.00  
003 03/19/2013 19.00  

我需要写一个SQL query以获得与每个ItemID的最新PurchaseDate相对应的Price. 表中的条目不一定按日期排序 像这样:

I need to write a SQL query to get the Price corresponding to the latest PurchaseDate for each ItemID. Entries in table might not necessarily be entered ordered by date Like this:

ItemID PurchaseDate Price  
001 03/19/2013 17.00  
002 03/18/2013 15.00  
003 03/19/2013 19.00  

推荐答案

子查询的思想是，它分别为每个ItemID获取最新的PurchaseDate.子查询的结果然后重新连接到表上，条件是该子查询在两个条件上匹配:ItemID和PurchaseDate.

The idea behind the subquery is it separately gets the latest PurchaseDate for each ItemID. The result of the subquery is then joined back on the table provided that it matches on two conditions: ItemID and PurchaseDate.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  ItemID, MAX(PurchaseDate) max_date
            FROM     TableName
            GROUP   BY ItemID
        ) b ON  a.ItemID = b.ItemID AND
                a.PurchaseDate = b.max_date

这篇关于获取每个项目的最新价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！