首页 › MariaDB优化与深入 › 如何高效返回每个infomation中info_comment得票数最多的commentContent

yongpiao 发表于 2010-2-21 21:21:15

如何高效返回每个infomation中info_comment得票数最多的commentContent

infomation信息表 字段 infoID
info_comment信息评论表   字段 commentID，infoID,commentContent
comment_vote 用户投票表    字段 commentID，infoID，voterID
如何高效返回每个infomation中info_comment得票数最多的commentContent？怎么用上索引？

yongpiao 发表于 2010-2-21 21:26:42

我自己写的方法，感觉很不好：
select * from info_comment d,
(SELECT *
FROM (

SELECT *
FROM (

SELECT infoID, commentID, count( commentID ) AS commentCount
FROM `comment_vote`
GROUP BY infoID, commentID
) AS a
ORDER BY infoID, commentCount DESC
) AS b
GROUP BY infoID) as c
where d.infoID=c.infoID and d.commentID=c.commentID

查看完整版本: 如何高效返回每个infomation中info_comment得票数最多的commentContent