MySQL记录有条件计数 [英] MySQL records count with condition

问题描述

我需要获取表格«posts»的记录计数，其中投票> = 5才能创建分页.

I need to get records count of table «posts» where votes>=5 to create pagination.

表格:

表帖子":user_id，post_n，可见性，类型

table «posts»: user_id, post_n, visibility, type

可见性值:0、1、2；键入值:'text'，'photo'…(它的枚举字段，有6个值)

visibility values: 0, 1, 2; type values: 'text', 'photo' … (it`s enum field, have 6 values)

表«votes»:voice_n，post_n，voter_id，表决

table «votes»: vote_n, post_n, voter_id, vote

投票值:-1或1

查询:

SELECT post_n, (SELECT SUM(vote) FROM votes WHERE votes.post_n=posts.post_n)AS votes
FROM posts WHERE visibility=2 AND type='text' HAVING votes>=5

时间0.4039

是否可以对其进行优化?

Is it possible to optimize it?

推荐答案

我认为如果没有子查询，您将获得更好的结果.您可以使用GROUP BY:

I think you would get better results without a subquery. You could do this by using GROUP BY:

SELECT p.post_n AS post_n, SUM(v.vote) AS votes
FROM posts p
INNER JOIN votes v ON (v.post_n = p.post_n)
WHERE p.visibility = 2 AND p.type = 'text'
GROUP BY p.post_n
HAVING SUM(v.vote) >= 5

这篇关于MySQL记录有条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！