mysql - Sql查询修正一对多表

Question

我有两个表，一个是用户，第二个是 user_education。一个用户可以有多个教育列表，所以我想获取最新的用户教育列表

users
===============
1-id
2-email

member_experience 
==============
1-id
2-user_id
3-designation

用户ID 1在user_education中有4个输入，所以我想获取用户的最后一条记录输入名称

原来的完整查询是这样的

SELECT u.id,u.name,u.gender,u.email,file_managed.file_name,file_managed.file_path 
from users as u 

INNER JOIN member_experience on (SELECT uid  FROM member_experience where member_experience.uid=u.id ORDER BY id DESC LIMIT 1)=u.id 

LEFT JOIN file_managed on file_managed.id= u.fid 

where u.user_type ='individual' AND u.gender='male'


"INNER JOIN member_experience on (SELECT uid  FROM member_experience where member_experience.uid=u.id ORDER BY id DESC LIMIT 1)=u.id "

这部分有问题，因为用户在member_experience表中有很多记录，但我只想获取最新的一条。

谢谢

Answer 1

将最后一条记录的获取下放到where语句中。

如果存在则删除表member_experience； 创建表member_experience(id int auto_increment主键, userid int);

insert into member_experience (userid) values
(1),(2),(1);

select * from member_experience

SELECT u.id,m.id 
from users as u 
join member_experience m on m.userid = u.id
where m.id = (SELECT max(m.id)  FROM member_experience m where m.userid = u.id)
order by u.id

或者如果您想包括那些没有经验的人

SELECT u.id,m.id 
from users as u 
left join member_experience m on m.userid = u.id
where (m.id = (SELECT max(m.id)  FROM member_experience m where m.userid = u.id)
        or m.id is null)

        and u.id < 4
order by u.id