如何使用join查询计算此表的获胜者

Question

candidate table  
---------------------------                                

Id    Name
1     James
2     peter
3     robin
4     max

vote table
-----------------------

vote   candidate id   

1       1
1       1
1       1
1       2
1       3
1       3
1       2

如何在mysql ....by这个数据库中统计获胜者

Answer 1

select 
c.name,
sum(v.vote) as total_votes
from candidate c
inner join vote v on v.candidate_id = c.Id
group by c.Id
order by total_votes desc limit 1

这将给出获胜者。

如果你想按顺序列出它们，也就是从上到下，使用如下命令

select 
c.name,
sum(v.vote) as total_votes
from candidate c
inner join vote v on v.candidate_id = c.Id
group by c.Id
order by total_votes desc

Answer 2

假设有两个人有相同的选票，那么在这种情况下就会有两个赢家，你可以这样做

SELECT  c.*,
COUNT(*) `votes`,
t.votes_total
FROM candidate c
LEFT JOIN vote v ON(v.candidate_id = c.id)
JOIN (SELECT COUNT(*) votes_total FROM vote 
      GROUP BY candidate_id 
      ORDER BY votes_total DESC
      LIMIT 1) t
GROUP BY  c.id
HAVING votes=t.votes_total
ORDER BY votes DESC

否则，如果您不关心多个赢家，那么您可以使用由Abhik Chakraborty提供的ORDER BY with LIMIT技术

Fiddle demo