mysql根据用户iD分组,并获取分组内的最新时间的一条记录
答
select * from 表名 group by 用户ID having max(时间);
答
方法一:
SELECT * FROM (
SELECT * FROM guess_detail ORDER BY update_time DESC) GROUP BY guess_user_id;
此方法适用于mysql 5.8以下,目前5.8版本分组内的排序会被忽略,所以不能使用。效率一般,小数据量可用。方法二更加通用,且效率更高。
方法二:
SELECT * FROM (
SELECT temp.*, @rn := IF(@prev = guess_user_id,@rn + 1,1) AS rn, @prev = guess_user_id FROM
(SELECT * FROM guess_detail ORDER BY update_time DESC) temp
JOIN (SELECT @rn := 0) AS vars
) t WHERE t.rn = 1;
此方法不仅可以获取最新的,还可以获取每组内最新的前几条(与rn=?有关)
答
是分组统计吗?按id分组再按时间排序最后取top1数据。。是这个意思吗?
答
mysql 和 sqlserver又没什么区别 直接 group by id order by time desc
答
select * from where group by id order by time desc 就是最新的
答
SELECT id, (select top 1 otherColumn from 表1 t2
where t1.id = t2.id
order by t2.time desc ) AS otherColumn
FROM 表1 AS t1
GROUP BY id;
sqlserver 语句
答
你的时间格式是什么?
答
SELECT MAX(update_time) FROM table GROUP BY guess_user_id;