mysql 两个as_mysql – 多次使用’as’

我有这个：

SELECT

posts.id,

(SELECT COUNT(*) FROM votes WHERE votes.post = posts.id) AS votesCount,

(SELECT SUM(vote) FROM votes WHERE votes.post = posts.id) AS votesUp

FROM posts WHERE posts.id = 1

如何在不执行其他SELECT的情况下获得votesCount和votesUp之间的区别？就像是：

SELECT

posts.id,

(SELECT COUNT(*) FROM votes WHERE votes.post = posts.id) AS votesCount,

(SELECT SUM(vote) FROM votes WHERE votes.post = posts.id) AS votesUp,

votesCount - votesUp AS votesDown

FROM posts WHERE posts.id = 1

是否有可能,或者我是否还要调用另一个SELECT？

谢谢.

顺便说一句,抱歉我的英语不好.

解决方法:

我建议您加入投票而不是使用子查询,然后您可以轻松地重用聚合：

select

p.id,

count(v.post) as votesCount,

sum(v.vote) as votesUp,

count(v.post) - sum(v.vote) as votesDown

from

posts p

left join votes v on v.post = p.id

where

p.id = 1

group by

p.id

标签：mysql

来源： https://codeday.me/bug/20190708/1404940.html