mysql计算select中的百分比

Question

我有一个收集选票的基本投票系统。制表应将赞成票总数除以总票数，以确定是否达到 2/3 多数。目前，我可以使用此查询返回数据

select sum(case when vote is not null and recruit_id=49631 then 1 else 0 end) 
as total_votes, 
sum(case when vote=1 and recruit_id=49631 then 1 else 0 end) as total_yes from votes;

返回

+-------------+-----------+
| total_votes | total_yes |
+-------------+-----------+
|           3 |         2 |
+-------------+-----------+

我想做的就是这样

+-------------+-----------+-----------+
| total_votes | total_yes | YESPercent|
+-------------+-----------+-----------+
|           3 |         2 |      66.6 |
+-------------+-----------+-----------+

我试过用这样的东西:

select sum(case when vote is not null and recruit_id=49631 then 1 else 0 end) as total_votes, 
sum(case when vote=1 and recruit_id=49631 then 1 else 0 end) as total_yes,
sum(total_votes,total_yes,(total_yes/total_votes)*100) as YESPercent from votes;

它无法识别最后部分的 total_yes 或 total_votes。有什么提示或好的指导链接吗？

Answer 1

恕我直言，最简洁的方法是在子查询中获得基本结果，然后在外部查询中使用它们进行计算。请注意，由于您只对两列中的 recruit_id = 49631 感兴趣，因此可以将此条件移至 where 子句。它还可能会略微提高查询的性能。作为另一项改进，您可以使用更直接的 count 而不是 sum，方法是利用其跳过 null 的质量:

SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent
FROM   (SELECT COUNT(vote) AS total_votes, 
               COUNT(CASE WHEN vote = 1 THEN 1 END) as total_yes,
         FROM  votes
         WHERE recruit_id = 49631) t