Leetcode 574 获胜候选人查询

Question

请参见错误截图图片

表：候选人

+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+  

表：投票

+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+
id is the auto-increment primary key, CandidateId is the id appeared in Candidate table. 

写一个sql来查找获胜候选人的名字，上面的例子将返回获胜者B。

+------+
| Name |
+------+
| B    |
+------+

笔记： 你可以假设没有平局，换句话说，最多只有一个获胜的候选人。

为什么这段代码不起作用？只是尝试无限制地使用

SELECT c.Name AS Name
FROM Candidate AS c 
JOIN 
(SELECT r.CandidateId AS can, MAX(r.Total_vote) AS big
FROM (SELECT CandidateId, COUNT(id) AS Total_vote
FROM Vote
GROUP BY CandidateId) AS r) AS v
ON c.id = v.can;

Answer 1

在您的查询中，在这里：SELECT r.CandidateId AS can, MAX(r.Total_vote) AS big

你使用MAX聚合函数，没有group by，这不是正确的 SQL。

尝试：

SELECT Candidate.* FROM Candidate
JOIN (
SELECT CandidateId, COUNT(id) AS Total_vote
FROM Vote
GROUP BY CandidateId 
ORDER BY COUNT(id) DESC LIMIT 1
) v
ON Candidate.id = v.CandidateId

Answer 2

这是一个join/group by查询，order by：

select c.name
from candidate c join
vote v
on v.candidateid = c.id
group by c.id, c.name
order by count(*) desc
limit 1;

Answer 3

SELECT c.Name AS Name
FROM Candidate AS c JOIN (SELECT r.CandidateId AS can
FROM
(SELECT CandidateId, COUNT(id) AS Total_vote
FROM Vote
GROUP BY CandidateId) AS r
WHERE r.Total_vote = (SELECT MAX(r.Total_vote) FROM (SELECT 
CandidateId, COUNT(id) AS Total_vote
FROM Vote
GROUP BY CandidateId) r)) AS v
ON c.id = v.can;

这是更新的代码

我的代码有两个错误。第一个是"如果选择列表中有任何非聚合列，则使用像 Max 这样的聚合需要一个 Group By 子句"，但不确定为什么我以前的代码仍然可以运行并且没有显示错误。也许系统会在运行时自动添加分组依据功能。

第二个是 max 不能与这种格式的分组依据一起使用。