获取单个值的最新日期 [英] Get a single value where the latest date

问题描述

我需要获取一件商品的最新价格(作为较大的select声明的一部分)，我不太清楚.

I need to get the latest price of an item (as part of a larger select statement) and I can't quite figure it out.

表格:

ITEMID    DATE       SALEPRICE
1         1/1/2014   10
1         2/2/2014   20
2         3/3/2014   15
2         4/4/2014   13 

根据上面的示例，当我查找项目1时，我需要将select的输出设置为'20'，当我查找项目2时，我需要将select的输出设置为'13'.

I need the output of the select to be '20' when looking for item 1 and '13' when looking for item 2 as per the above example.

我正在使用Oracle SQL

I am using Oracle SQL

推荐答案

(在我看来)最易读/可理解的SQL是:

The most readable/understandable SQL (in my opinion) would be this:

select salesprice from `table` t
where t.date = 
(
  select max(date) from `table` t2 where t2.itemid = t.itemid
)
and t.itemid = 1 -- change item id here;

假设表的名称为table，并且每天每个商品只有一个价格(否则where条件匹配每个商品多于一行).另外，子选择可以写为自联接(不应影响性能).

assuming your table's name is table and you only have one price per day and item (else the where condition would match more than one row per item). Alternatively, the subselect could be written as a self-join (should not make a difference in performance).

我不确定其他答案使用的OVER/PARTITION.也许可以根据DBMS对其进行优化以提高性能.

I'm not sure about the OVER/PARTITION used by the other answers. Maybe they could be optimized to better performance depending on the DBMS.

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