在 MySQL 中统计票数

Question

我有一个表，包含以下列： id name vote

每个名称都是唯一的，每个投票要么为空，要么包含一个名称。

我需要一个 MySQL 语句来返回每个人有多少票，以及谁为每个人投票。

我已经为此工作了 3 个小时，我完全不知所措，所以说实话，我不在乎它的效率有多低，或者你是如何做到的。

Answer 1

SELECT name, count(*) as num_votes, GROUP_CONCAT(vote) as voted_by FROM table GROUP BY 1

Answer 2

多少票：

select 
 count(*) as numVotes, 
 vote
from
 voteTable
where
 vote IS NOT NULL
group by 
 vote
order by
 numVotes desc

谁投票了：

Select
 name,
 vote
from
 voteTable

......除非我误读了什么，否则应该这么简单

Answer 3

select count(name), id from your_table where vote is not null group by (name) 

Answer 4

要获取每个人的票数：

select vote, 
       count(*) as nbr_of_votes 
  from table 
 where vote is not null 
 group by vote 
 order by nbr_of_votes desc;

要获取谁投票给谁，您基本上必须选择整个表，忽略空值

select vote, 
       name 
  from table
 where vote is not null 
 order by vote;

Answer 5

SELECT VOTE, COUNT(1) Number_Of_Votes,
GROUP_CONCAT(Name)
WHERE VOTE is Not NULL
GROUP BY VOTE