这是我的photo表的结构
CREATE TABLE `photo` (
`id` bigint(20) NOT NULL,
`uptime` datetime default NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK65B3E3248CE7A33` (`user_id`), CONSTRAINT `FK65B3E3248CE7A33` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
) 我想从photo表中找到 以userid分组的,且上传时间最晚的记录
也就是查出来每个user 最新上传的photo
CREATE TABLE `photo` (
`id` bigint(20) NOT NULL,
`uptime` datetime default NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK65B3E3248CE7A33` (`user_id`), CONSTRAINT `FK65B3E3248CE7A33` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
) 我想从photo表中找到 以userid分组的,且上传时间最晚的记录
也就是查出来每个user 最新上传的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
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 *
from photo a
where not exists (select 1 from photo where user_id=a.user_id and uptime>a.uptime)
select a.id,a.uptime,a.user_id from photo a
left join photo b on a.userid=b.userid and a.uptime<=b.uptime
group by a.id,a.uptime,a.user_id having count(b.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;