mysql - 在选择中选择另一个表的第一条记录 - MYSQL

Question

我正在尝试列出所有客户的查询，并在单个查询中获取 history_client 表中的最后一条评论和该评论的日期，以便将其列出。

select a.id_client,a.name,a.lastname,(select b.date_created,b.comentary 
from history_of_client b where a.id_client = b.id_client_asociate) from clients_main_table

标签： mysql

Answer 1

您可以在 max(date_created) 上为历史表上的 id_client 使用内部连接并加入

SELECT a.id_client,a.name,a.lastname, h.commentary
FROM clients_main_table a 
INNER join (
  select b.id_client_asociate, max(b.date_created) max_date
  from history_of_client 
  group by  b.id_client_asociate ) t on t.id_client_asociate = a.id_client 
INNER JOIN history_of_client h on h.id_client_asociate = t.id_client_asociate 
      and h.date_created = t.max_date