Leetcode574. 当选者（中等）

题目
表: Candidate

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

表: Vote

+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+

id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B.

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

注意:

你可以假设没有平局，换言之，最多只有一位当选者。

生成数据

CREATE TABLE Candidate(
id INT,
NAME VARCHAR(10));

INSERT INTO Candidate VALUE(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

CREATE TABLE Vote(
id INT,
CandidateId  INT
);

INSERT INTO Vote VALUE(1,2),(2,4),(3,3),(4,2),(5,5);

解答
两表连接

select V.name  
from Vote as V
left join Candidate as C
on V.CandidateId = C.id
group by V.name
order by count(C.name) desc
limit 0,1;

利用子查询
先查出投票最多的CandidateId

select V.CandidateId 
from Vote as V
group by V.CandidateId
order by count(V.CandidateId) desc
limit 0,1;

选出对应id的姓名

select C.Name
from  Candidate as C
where C.id = (select V.CandidateId 
from Vote as V
group by V.CandidateId
order by count(V.CandidateId) desc
limit 0,1)

同理

select C.Name
from Candidate as C join
(
    select V.CandidateId,count(V.id) as cnt
    from Vote as V
    group by V.CandidateId
    order by cnt desc
    limit 0,1
) as A
on (C.id = A.CandidateId)

看到别人的解答中出现了一种情况 Vote表中的某些CandidateId可能不在Candidate 表中

感觉这种情况不存在呀 。。。