我有一个基本的投票系统,用于收集投票。计票应该采用总是赞成票数除以总票数的方式来确定是否达到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。有什么提示或好的指导链接吗?
'FROM votes WHERE recruit_id = 49631) t'
。我修改了它,以t;作为结尾,结果相同。我还在尝试调整代码,试图弄清楚问题所在。感谢您提供的有关使用计数忽略空值的提示,它确实加快了我的查询时间! - drizselect 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