select userid,max(createtime) from table group by userid
select userid,max(createtime) from table group by userid
不行的话,可以用两条语句解决 .. select top 5 distinct 评论ID,用户ID from table 假如查处评论ID:1,2,3,4,5 再查:select top 5 * from table where 评论ID in (1,2,3,4,5)
哦,呵呵,你可以关联啊 select * from table where id in (select id from ( select id,userid,max(createtime) from table group by userid ) tablea )
恩.和我的意思一样 select * from table where 评论ID in (select 评论ID from (select distinct top 5 评论ID,用户ID from table order by 评论ID) a)
评论表:comments 用户表:users(环境:mysql) select a.comment_id ,max(a.comment_date), a.comment_text, a.user_id, a.user_name from ( select c.comment_id, c.comment_date, c.comment_text, u.user_id, u.user_name from comments c, users u where c.user_id = u.user_id order by comment_date desc limit 5 ) a group by a.user_id order by max(a.comment_date) desc;
没有直接用DISTINCT不就好了。..
select top 5 distinct 评论ID,用户ID from table
假如查处评论ID:1,2,3,4,5
再查:select top 5 * from table where 评论ID in (1,2,3,4,5)
select * from table where id in (select id from (
select id,userid,max(createtime) from table group by userid ) tablea )
(select 评论ID from
(select distinct top 5 评论ID,用户ID from table order by 评论ID) a)
select a.comment_id ,max(a.comment_date), a.comment_text, a.user_id, a.user_name
from
(
select c.comment_id, c.comment_date, c.comment_text, u.user_id, u.user_name
from comments c, users u
where c.user_id = u.user_id
order by comment_date desc
limit 5
) a
group by a.user_id
order by max(a.comment_date) desc;
第一步:按时间倒排序,取出比预期多一些的记录来,比如100条,你可以用Hibernater的方式,控制返回的记录数量
Query query = session.createQuery(hql);
if(ai_PageSize > 0) {
query.setFirstResult(0);
query.setMaxResults(100);
}
List comments = query.list();
第二步:创建一个真正要返回的结果集List,循环这一百条(comments ),把评论对象加到集合里,只是添加前要判断一下 ,如果集合里已存在这个用户,则跳到下一步continue,如果已取满5个break跳出;就可以了.