// id相同,update_time不同,取update_time最后一条
1. 使用 NOT EXISTS参数
SELECT id, user_id,image_url FROM file a WHERE NOT EXISTS (SELECT 1 FROM file where a.user_id = user_id AND a.update_time < update_time)
2. 使用子查询
SELECT id, user_id,image_url,update_time FROM file WHERE create_time IN (SELECT max(update_time) FROM file group by user_id)
查询结果:
3、关联查询
-- mysql 5.7.5 及以上版本
select user_id,update_time,id, image_url from (SELECT
ANY_VALUE(t1.id) as id,
t1.user_id,
ANY_VALUE(t1.update_time) as update_time,
ANY_VALUE(t1.image_url) as image_url
FROM
file t1
INNER JOIN ( SELECT t2.user_id, max( t2.update_time ) AS update_time FROM file t2 where t2.`status` = 1 GROUP BY t2.USER_ID ) AS t3
ON t1.user_id = t3.USER_ID AND t1.update_time = t3.update_time
GROUP BY t1.user_id ) ss order by update_time desc