如何在 postgres 的 order by 子句中使用两个别名的总和?

Question

这是我到目前为止所拥有的...

SELECT 
*, 
(SELECT COUNT(*) FROM upvote WHERE post_id = post.id) AS upvotes,
(SELECT COUNT(*) FROM downvote WHERE post_id = post.id) AS downvotes
FROM 
post
ORDER BY 
upvotes DESC;

。但我希望能够ORDER BYupvotes和downvotes的总和.这在Postgres上可能吗？我使用的是版本 12。

Answer 1

这样做：

select * from (
SELECT *, 
(SELECT COUNT(*) FROM upvote up WHERE up.post_id = post.id) AS upvotes,
(SELECT COUNT(*) FROM downvote dw WHERE dw.post_id = post.id) AS downvotes
FROM post) A
ORDER BY (upvotes+downvotes) DESC;

下面是 Postgresql 12 的一个小演示。

Answer 2

如果您想要所有帖子的赞成票和反对票数(有投票(，我建议：

select postid, sum(upvote) as upvotes, sum(downvote) as downvotes
from ((select post_id, 1 as upvote, 0 as downvote
from upvotes
) union all
(select post_id, 0, 1
from downvotes
)
) ud
group by postid;

可以在查询末尾添加order by子句：

order by count(*) desc