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

 

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

 

  1. SELECT id, newsID, comment, MAX(theTime)    
  2. FROM comments    
  3. 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

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值