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。有什么提示或好的指导链接吗？

- driz

Answer 1

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

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

- Mureinik

---

将其直接粘贴到结果中会得到 'FROM votes WHERE recruit_id = 49631) t'。我修改了它，以t;作为结尾，结果相同。我还在尝试调整代码，试图弄清楚问题所在。感谢您提供的有关使用计数忽略空值的提示，它确实加快了我的查询时间！ - driz

我通过这样做使它工作了，我需要添加更多的数据到表中以确保它按预期工作，但还是谢谢！select sum(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) as t; - driz

Answer 2

基本上，您只需要将原始查询作为子查询即可实现此目的：

SELECT total_votes, total_yes, sum(total_votes,total_yes,(total_yes/total_votes)*100) as YESPercent
FROM
(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) as v;

- wogsland

Answer 3

根据SQL方言的不同，别名可以重复使用或不可重复。在mysql中，您受到限制。解决方案是子查询：

SELECT total_votes,total_yes,sum(total_votes,total_yes,
(total_yes/total_votes)*100) as YESPercent 
FROM (
  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) a;

- Norbert