Question

574. 当选者

English Version

题目描述

表: Candidate

+-------------+----------+
| Column Name | Type   |
+-------------+----------+
| id      | int    |
| name    | varchar  |
+-------------+----------+
id 是该表中具有唯一值的列
该表的每一行都包含关于候选对象的id和名称的信息。

 

表: Vote

+-------------+------+
| Column Name | Type |
+-------------+------+
| id      | int  |
| candidateId | int  |
+-------------+------+
id 是自动递增的主键(具有唯一值的列)。
candidateId是id来自Candidate表的外键(reference 列)。
该表的每一行决定了在选举中获得第i张选票的候选人。

 

编写解决方案来报告获胜候选人的名字(即获得最多选票的候选人)。

生成的测试用例保证 只有一个候选人赢得 选举。

返回结果格式如下所示。

 

示例 1:

输入: 
Candidate table:
+----+------+
| id | name |
+----+------+
| 1  | A  |
| 2  | B  |
| 3  | C  |
| 4  | D  |
| 5  | E  |
+----+------+
Vote table:
+----+-------------+
| id | candidateId |
+----+-------------+
| 1  | 2       |
| 2  | 4       |
| 3  | 3       |
| 4  | 2       |
| 5  | 5       |
+----+-------------+
输出: 
+------+
| name |
+------+
| B  |
+------+
解释: 
候选人B有2票。候选人C、D、E各有1票。
获胜者是候选人B。

解法

方法一

# Write your MySQL query statement below
SELECT
  Name
FROM
  (
    SELECT
      CandidateId AS id
    FROM Vote
    GROUP BY CandidateId
    ORDER BY COUNT(id) DESC
    LIMIT 1
  ) AS t
  INNER JOIN Candidate AS c ON t.id = c.id;

方法二

# Write your MySQL query statement below
SELECT name
FROM
  Candidate AS c
  LEFT JOIN Vote AS v ON c.id = v.candidateId
GROUP BY c.id
ORDER BY COUNT(1) DESC
LIMIT 1;