MySQL查询加权投票-如何计算分配给不同列的值

Question

我有一个投票应用程序，它将值写入mysql数据库表。这是一个偏好/加权投票系统，所以人们选择第一选择，第二选择和第三选择。这些都在表中单独的字段中。我正在寻找一种方法来编写一个查询，该查询将为响应分配数值(第一次响应为3，第二次响应为2，第一次响应为1)，然后显示总和分数的值。我已经能够为总票数做这个

select count(name) as votes,name 
from (select 1st_option as name from votes 
      union all 
      select 2nd_option from votes 
      union all 
      select 3rd_option from votes) as tbl 
group by name 
having count(name) > 0 
order by 1 desc;

，但还没有完全弄清楚如何为每列中的响应分配值，然后将它们放在一起。非常感谢任何帮助。谢谢!

Answer 1

你可以这样做:

select sum(score) as votes,name 
from (select 1st_option as name, 3 as score from votes 
      union all 
      select 2nd_option as name, 2 as score from votes 
      union all 
      select 3rd_option as name, 1 as score from votes) as tbl 
group by name;