我如何在这种情况下需要更改我的sql来获取我想要的内容？ [英] How do I need to change my sql to get what I want in this case?

问题描述

我有一张如下表格：

  id值日期
 1 5 2015-01-10 
 2 5 2015-06-13 
 3 5 2015-09-05 
 4 11 2015-02-11 
 5 11 2015-01-10 
 6 11 2015- 01-25 
  

可以看出，每个值用不同的日期出现3次。我想写一个查询，它返回具有最大值日期的唯一值 s，上表：

  id值日期
 3 5 2015-09-05 
 4 11 2015- 02-11 
  

我该怎么做？

这是更新后的问题：

我遇到的真正问题比上面的简化版本要复杂一点。一旦我知道简化版本的答案，我想我可以进一步推动，但我的客人我错了。所以，我更新了这里的问题。

我有2个表格，如下所示：

 表1 
 id id2日期
 1 2 2015-01-10 
 2 5 2015-06-13 
 3 9 2015-09-05 
 4 10 2015-02-11 
 5 26 2015-01-10 
 6 65 2015-01-25 
 
表2 
 id id2数据
 1 2 A 
 2 5 A 
 3 9 A 
 4 10 B 
 5 26 B 
 6 65 B 
  

这里，表1 和表2 加入了 id2

我想要的是两条记录，如下所示：

  id2日期数据
 9 2015-01-10 A 
 10 2015-02-11 B 
 > row_number 来选择 

解决方案选择每个值的日期最大的行

  select * from（
 select t2.id2，t1.date， t2.data，
 row_number（）over（由t2.data分割，由t1.date desc分割）rn 
 from table1 t1 
 join table2 t2 on t1.id = t2.id2 
）t其中rn = 1 
  

I have a table like following:

id  value    date
1    5    2015-01-10
2    5    2015-06-13
3    5    2015-09-05
4    11   2015-02-11
5    11   2015-01-10
6    11   2015-01-25

As can be seen, every value appears 3 times with different date. I want to write a query that returns the unique values that has the maximum date, which would be the following for the above table:

id  value    date
3    5    2015-09-05
4    11   2015-02-11

How could I do it?

This is the updated question:

The real question I am encountering is a little bit more complicated than the simplified version above. I thought I can move a step further once I know the answer to the simplified version, but I guest I was wrong. So, I am updating the question herein.

I have 2 tables like following:

      Table 1
id  id2    date
1    2   2015-01-10
2    5   2015-06-13
3    9   2015-09-05
4    10  2015-02-11
5    26  2015-01-10
6    65  2015-01-25

      Table 2
id  id2    data
1    2       A
2    5       A
3    9       A
4    10      B
5    26      B
6    65      B

Here, Table 1 and Table 2 are joined by id2

What I want to get is two records as follows:

id2  date       data
9   2015-01-10    A
10  2015-02-11    B

解决方案

You can use row_number to select the rows with the greatest date per value

select * from (
    select t2.id2, t1.date, t2.data, 
        row_number() over (partition by t2.data order by t1.date desc) rn
    from table1 t1
    join table2 t2 on t1.id = t2.id2
) t where rn = 1

这篇关于我如何在这种情况下需要更改我的sql来获取我想要的内容？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！