每组项目选择最新的 [英] Selecting the latest per group of items

问题描述

可能重复:
检索每个组中的最后一条记录

Possible Duplicate:
Retrieving the last record in each group

我有2个表产品和价格

产品

ProdCode - PK
ProdName

费用

Effectivedate - PK
RetailCOst
Prodcode

我尝试了以下查询:

SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail 
FROM cost a 
INNER JOIN product b USING (ProdCode)
WHERE EffectiveDate <= '2009-10-01'
GROUP BY a.ProdCode;

嗯，它显示了正确的生效日期，但该特定生效日期的费用不匹配.

uhm yah its showing the right effectivedate but the cost on that specific effectivedate doesnt match.

所以我想选择每个项目的匹配成本的最新日期.

so i want to select the latest date with the matching cost per item.

例如，我选择的日期为"2009-12-25"，其中一项的记录是:

for example the date i selected is '2009-12-25' and the records for 1 item are these:

ProdCode |EffectiveDate| Cost
00010000 | 2009-01-05  |    50
00010000 | 2009-05-25  |    48
00010000 | 2010-07-01  |    40

因此，结果我应该得到00010000|2009-05-25|48，因为它小于查询中的日期，并且是该项目的最新日期.然后我想在查询中显示每种产品的最新费用.

so in result i should get 00010000|2009-05-25|48 because it is lesser than the date on my query and it is the latest for that item. and then i want to to show on my query the latest costs on each product.

希望很快能收到您的来信！谢谢！

hope to hear from you soon! thanks!

推荐答案

或者，使用旧的max concat技巧也可以解决问题.

Alternatively, using the old max concat trick should do the trick.

SELECT
  p.ProdCode,
  SUBSTRING(MAX(CONCAT(d.EffectiveDate, c.RetailCost)), 1, 10) AS date,
  SUBSTRING(MAX(CONCAT(d.EffectiveDate, c.RetailCost)), 10, 100) + 0 AS cost
FROM
  product  p,
  cost     c
WHERE
  p.ProdCode = c.ProdCode AND
  c.EffectiveDate < '2009-10-01'
GROUP BY   
  p.ProdCode

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