加入第二个包含多个记录的表，取最新的

I have two tables:

我有两个表:

person_id | name
1            name1
2            name2
3            name3

and a second table:

和第二个表:

person_id | date     | balance
1           2016-03     1200                    ---- \
1           2016-04     700                     ----  > same person
1           2016-05     400                     ---- /
3           2016-05     4000

Considering that person_id 1 has three record on the second table how can I join the first just by taking the latest record? (that is: balance 400, corresponding to date: 2016-05).

考虑到person_id 1在第二个表上有三个记录，我怎么能仅仅通过获取最新的记录就加入第一个记录呢?(即:余额400，对应日期:2016-05)。

E.g.: query output:

例如:查询输出:

person_id | name    | balance
1           name1     400
2           name2     ---
3           name3     4000

if it's possibile prefer the simplicity over the complexity of the solution

如果可能的话，我更喜欢简单而不是复杂的解决方案

2 个解决方案

#1

A query working for all DB engines is

为所有DB引擎工作的查询是

select t1.name, t2.person_id, t2.balance
from table1 t1
join table2 t2 on t1.person_id = t2.person_id
join
(
    select person_id, max(date) as mdate
    from table2
    group by person_id
) t3 on t2.person_id = t3.person_id and t2.date = t3.mdate

#2

The best way to do this in any database that supports the ANSI standard window functions (which is most of them) is:

在任何支持ANSI标准窗口函数(其中大多数)的数据库中做到这一点的最佳方法是:

select t1.*, t2.balance
from table1 t1 left join
     (select t2.*,
             row_number() over (partition by person_id order by date desc) as seqnum
      from table2 t2
     ) t2
     on t1.person_id = t2.person_id and seqnum = 1;

#1