--获取最新时间的某条数据(下面这个是错的,玛德浪费一天,看原文吧)
- 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