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

末蓝、 2022-07-12 11:42 173阅读 0赞

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

1.  **SELECT** id, newsID, comment, **MAX**(theTime)    
2.  **FROM** comments    
3.  **GROUP** **BY** newsID

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

借鉴原文的方法一

selet tt.id,tt.newsID,tt.comment,tt.theTime from(  
    select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID

。

这个我的业务写出的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) rre
    GROUP BY topicId
    ORDER BY create_date DESC

[http_blog.csdn.net_shellching_article_details_8292338]: http://blog.csdn.net/shellching/article/details/8292338