时间戳

首页 > 解决方案 > leetcode 574 获胜候选查询

mysql - leetcode 574 获胜候选查询

问题描述

错误截图请看图片

表：候选人

+-----+---------+
| 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;

标签： mysqlsql

解决方案

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 不能以这种格式与 Group by 一起使用。

推荐阅读