mysql - 多次使用 'as'

Question

我有这个:

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？谢谢。

顺便说一句，抱歉我的英语不好。

Answer 1

我建议你加入投票而不是使用子查询，这样你就可以轻松地重用聚合:

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