SQL查询以获取最近的项目

I have a sql table

我有一个sql表

id  item    date
A   apple   2017-09-17
A   banana  2017-08-10
A   orange  2017-10-01
B   banana  2015-06-17
B   apple   2014-06-18

How do I write a sql query, so that for each id I get the two most recent items based on date. ex:

如何编写SQL查询，以便对于每个id，我根据日期获得最近的两个项目。例如：

id  recent second_recent   
a   orange  apple
b   banana  apple

3 个解决方案

#1

You can use row_number() and conditional aggregation:

您可以使用row_number（）和条件聚合：

select id,
       max(case when seqnum = 1 then item end) as most_recent,
       max(case when seqnum = 2 then item end) as most_recent_but_one,
from (select t.*, 
            row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
group by id;

#2

Like said on: SQL: Group by minimum value in one field while selecting distinct rows

如上所述：SQL：在一个字段中按最小值分组，同时选择不同的行

You must use A group By to get min

你必须使用A组来获得分钟

SELECT mt.*,     
FROM MyTable mt INNER JOIN
    (
        SELECT item AS recent, MIN(date) MinDate, ID
        FROM MyTable
        GROUP BY ID
    ) t ON mt.ID = t.ID AND mt.date = t.MinDate

I think you can do the same with a order by to get two value instead of one

我认为您可以通过获取两个值而不是一个来执行相同的操作

#3

You can use Pivot table

您可以使用数据透视表

SELECT first_column AS <first_column_alias>,
 [pivot_value1], [pivot_value2], ... [pivot_value_n]
 FROM
 (<source_table>) AS <source_table_alias>
 PIVOT
 (
 aggregate_function(<aggregate_column>)
 FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
 ) AS <pivot_table_alias>;

Learn More with example here Example

通过示例了解更多示例

#1