select * from table GROUP BY classid order by id desc limit 10
楼上的你也明显不对.你想想.按classid分组了,后面的 ordery id desc 肯定没用了.
按classid分组然后把分组后的数据order by id是取出最后十条。 怎么就肯定没用了????
好像就这个sql已经让我拿过好几次分了~:)mysql 4.1 以上:select * from table where classid, postdate in ( select classid,max(postdate) from table group by classid ) order by postdate desc limit 10
上面少了括号:select * from table where (classid, postdate) in ( select classid,max(postdate) from table group by classid ) order by postdate desc limit 10
看了楼上的查询才知道,原来mysql4.1以上就支持子查询了-_-||
如果你的ID是自增型的,上面的方法也可以;如果不是自增型的至少你的表里应该有时间字段吧??假设时间字段为dateline,可以像下面这样写. SELECT * FROM `tt` GROUP BY classid ORDER BY dateline DESC LIMIT 0 , 10
我的MYSQL是不支持子查询的.所以,请问有别的办法吗
上面我的方法有问题,可能这样更好一些。SELECT DISTINCT * FROM (SELECT * FROM `tt` ORDER BY dateline DESC ) AS `t` GROUP BY classid ORDER BY dateline DESC LIMIT 0 , 10
怎么就肯定没用了????
where classid, postdate in
( select classid,max(postdate) from table group by classid )
order by postdate desc
limit 10
where (classid, postdate) in
( select classid,max(postdate) from table group by classid )
order by postdate desc
limit 10
SELECT *
FROM `tt`
GROUP BY classid
ORDER BY dateline DESC
LIMIT 0 , 10
GROUP BY classid
ORDER BY dateline DESC
LIMIT 0 , 10
那我劝你还是用程序去控制吧.....
先把纪录按时间排序查出来,然后程序按classid遍历一次,滤掉classid重复的行.