group by分组内排序，取其中一条

来源：互联网发布：阿里云短信接口 php 编辑：程序博客网时间：2024/05/22 09:01

--获取最新时间的某条数据（下面这个是错的，玛德浪费一天，看原文吧）

SELECT id, newsID, comment, MAX(theTime)
FROM comments
GROUP BY newsID

感谢原文：http://blog.csdn.net/shellching/article/details/8292338

这个我的业务写出的sql，着重是尾部group by和order by

SELECT     rre.*FROM    (SELECT         re.*,            IFNULL(ti.id, UUID()) topicId,            ti.title,            IFNULL(ti.`type`, '2') type,            (SELECT                     COUNT(1)                FROM                    topic_video_extends                WHERE                    extend_key = 'topic'                        AND extend_value = ti.id) joinCount,            (SELECT                     COUNT(1)                FROM                    um_user_extends                WHERE                    extend_key = 'attention_topic'                        AND extend_value = ti.id) attCount,            (re.zanCount + re.collectCount + 2 * commentCount) rank    FROM        (SELECT         tv.id videoId,            tv.cover videoCover,            tv.video_url videoUrl,            tv.duration,            tv.zan_count zanCount,            tv.collect_count collectCount,            (SELECT                     COUNT(1)                FROM                    topic_video_comment                WHERE                    relate_id = tv.id) commentCount,            (SELECT                     COUNT(1)                FROM                    um_user_extends                WHERE                    user_id = '558628a324c6450aadd4c2f5a37a1cc6'                        AND extend_key = 'video_collect'                        AND extend_value = tv.id) isCollect,            (SELECT                     COUNT(1)                FROM                    um_user_extends                WHERE                    user_id = '558628a324c6450aadd4c2f5a37a1cc6'                        AND extend_key = 'video_zan'                        AND extend_value = tv.id) isZan,            tv.create_date,            tv.is_share,            uui.user_id userId,            uui.user_name userName,            uui.head_url headUrl    FROM        topic_video tv, um_user_info uui    WHERE        uui.user_id = tv.user_id            AND tv.status = 1            AND tv.is_public = 0            AND tv.origin = 0            AND tv.user_id = '558628a324c6450aadd4c2f5a37a1cc6') re    LEFT JOIN topic_video_extends tie ON tie.video_id = re.videoId    LEFT JOIN topic_info ti ON ti.id = tie.extend_value    WHERE        tie.extend_key = 'topic'    GROUP BY topicId , rank    ORDER BY rank DESC) rreGROUP BY topicIdORDER BY create_date DESC

1 0