56,677
社区成员
发帖
与我相关
我的任务
分享
看你的表结构,应该你的photo表类似一个日志表,所以,上传时间最晚的记录也就是ID值最大的记录:
写法1:
select a.*,b.* from photo a,`user` b where a.user_id=b.id and not exists (
select 1 from photo c where c.user_id=a.user_id and c.id>a.id);
写法2:
select a.*,b.* from photo a,`user` b where a.user_id=b.id and a.id=(select max(c.id) from photo c where c.user_id=a.user_id);
写法3:
select a.*,b.* from photo a,`user` b,(select user_id,max(id) as newid from photo group by user_id) c where a.user_id=b.id and a.id=c.newid;
select *
from photo a
where not exists (select 1 from photo where user_id=a.user_id and uptime>a.uptime)
select photo.*
from photo inner join (select user_id,max(uptime) as max_uptime from photo group by user_id) p
on photo.user_id=p.user_id and photo.uptime=p.max_uptime
select photo.*
from photo,(select user_id,max(uptime) as max_uptime from photo group by user_id) p
where photo.user_id=p.user_id
and photo.uptime=p.max_uptime