SQL查询来获取所有不同的项目最新记录表中的

Question

我定义的所有销售的表像：

mysql> describe saledata; 
+-------------------+---------------------+------+-----+---------+-------+ 
| Field    | Type    | Null | Key | Default | Extra | 
+-------------------+---------------------+------+-----+---------+-------+ 
| SaleDate   | datetime   | NO |  | NULL |  | 
| StoreID   | bigint(20) unsigned | NO |  | NULL |  | 
| Quantity   | int(10) unsigned | NO |  | NULL |  | 
| Price    | decimal(19,4)  | NO |  | NULL |  | 
| ItemID   | bigint(20) unsigned | NO |  | NULL |  | 
+-------------------+---------------------+------+-----+---------+-------+ 

我需要得到最后的销售价格对所有项目（如价格可能改变）。我知道我可以运行像查询：

SELECT price FROM saledata WHERE itemID = 1234 AND storeID = 111 ORDER BY saledate DESC LIMIT 1 

不过，我希望能够获得最后的销售价格（对ItemID s的存储在一个单独的项目表）中的所有项目，并把它们插入到一个单独的表。我怎样才能得到这些数据？我试过这样的查询：

SELECT storeID, itemID, price FROM saledata WHERE itemID IN (SELECT itemID from itemmap) ORDER BY saledate DESC LIMIT 1 

然后将其封装到插入，但它没有得到正确的数据。是否有一个查询我可以运行，以获得最后的价格为每个项目，并插入到表中这样定义：

mysql> describe lastsale; 
+-------------------+---------------------+------+-----+---------+-------+ 
| Field    | Type    | Null | Key | Default | Extra | 
+-------------------+---------------------+------+-----+---------+-------+ 
| StoreID   | bigint(20) unsigned | NO |  | NULL |  | 
| Price    | decimal(19,4)  | NO |  | NULL |  | 
| ItemID   | bigint(20) unsigned | NO |  | NULL |  | 
+-------------------+---------------------+------+-----+---------+-------+ 

Answer 1

这是频繁出现堆栈溢出的最大正每组问题。

INSERT INTO lastsale (StoreID, Price, ItemID) 
    SELECT s1.StoreID, s1.Price, s1.ItemID 
    FROM saledata s1 
    LEFT OUTER JOIN saledata s2 
    ON (s1.Itemid = s2.Itemid AND s1.SaleDate < s2.SaleDate) 
    WHERE s2.ItemID IS NULL;