如何通过mysql创建这样的表

时间:2022-10-14 01:10:59

I have a table like this:

我有这样一张桌子:

ID    Payment      year
A      10           1
A      15           2
A      12           3
B      11           2
B      15           4
C      25           1
C      17           3 

I'm looking for a query that returns row for each ID for the its last year. The year column is ordered increasing for each ID.

我正在寻找一个查询,它返回去年每个ID的行。每个ID的年份列都会增加。

I need a result like this:

我需要这样的结果:

ID    Payment    year
 A      12        3
 B      15        4
 C      17        3

I have this query so far:

到目前为止我有这个问题:

select ID, Payment, Year from payment_table
where year = (select max(year) from ?????????);

I don't know what shall I write instead of ????????

我不知道该怎么写而不是????????

It would be appreciated If anybody gives me some idea.

如果有人给我一些想法,将不胜感激。

1 个解决方案

#1


3  

Use subquery :

使用子查询:

select t.*
from table t
where year = (select max(t1.year) from table t1 where t1.id = t.id);

#1


3  

Use subquery :

使用子查询:

select t.*
from table t
where year = (select max(t1.year) from table t1 where t1.id = t.id);