从两个表导出数据，但只导出最后一条记录

Question

我有一个表user(为了清晰起见减少):

id名称

尝试下一个查询:

select 
u.name, 
ug.weight 
from users u 
inner join user_goal ug on ug.user_id = u.id
inner join (
select max(id) last_id from user_goal group by user_id
) last_value on last_value.last_id = ug.id;

Test SQL online

在MySQL 8.0的情况下，你可以使用窗口函数row_number()作为方法:

select name, weight from (
select 
u.name, 
ug.weight,
row_number() over (partition by ug.user_id order by ug.id desc) rn
from users u 
inner join user_goal ug on ug.user_id = u.id
) t where rn = 1;

SQL这里