mysql - 每一个分类只调用一条新闻

Question

SELECT `a`.`tid`, `a`.`title`, `a`.`cid`, `a`.`updatetime`, `b`.`uid`, `b`.`username` FROM (`news` a) LEFT JOIN `users` b ON `b`.`uid` = `a`.`uid` WHERE `cid` IN ('10', '9', '8', '6', '4', '11', '7', '5') ORDER BY `a`.`updatetime` desc LIMIT 8

如何每个分类只调用一条最新记录，如果使用group by的话，可以实现，、只是无法按最新的排序了。有什么好办法吗

Answer 1

假设cid是你说的分类，那么应该可以这样：

SELECT * from 
( SELECT `a`.`tid`, `a`.`title`, `a`.`cid`, `a`.`updatetime`, `b`.`uid`, `b`.`username` 
FROM (`news` a) LEFT JOIN `users` b ON `b`.`uid` = `a`.`uid` 
WHERE `cid` IN ('10', '9', '8', '6', '4', '11', '7', '5') 
ORDER BY a.cid, `a`.`updatetime` DESC ) alias
GROUP BY a.cid

Answer 2

SELECT `a`.`tid`, `a`.`title`, `a`.`cid`, `a`.`updatetime` FROM `news` a WHERE `a`.`update_time`=(SELECT MAX(`updatetime`) FROM `news` tmp_news WHERE tmp_news.`tid`=a.`tid`);