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

最新推荐文章于 2024-07-21 00:07:07 发布

多来哈米

最新推荐文章于 2024-07-21 00:07:07 发布

阅读量9.9k

点赞数 1

分类专栏：数据库（mysql、oracle）

数据库（mysql、oracle）专栏收录该内容

49 篇文章 1 订阅

订阅专栏

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

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

感谢原文：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