mysql - 将最新值加入表

Question

我正在使用mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper.

我有一张叫桌子companies和一张桌子fundamentals。

我想将 的最新值加入fundamentals表companies中，而最新的是created_atin fundamentals。

请在下面找到我在 db-fiddle 上的最小示例：

db-fiddle 示例

我当前的查询如下所示：

select
    *
from
    companies c
left outer join fundamentals f on
    f.companies_id = c.id
order by
    f.created_at DESC;

感谢您的回复！

标签： mysqlsql

Answer 1

您可以使用窗口函数：

select *
from companies c left outer join
     (select f.*,
             row_number() over (partition by f.companies_id order by f.created_at desc) as seqnum
      from fundamentals f
     ) f
     on f.companies_id = c.id and f.seqnum = 1
order by f.created_at DESC;