大数据第45天—Mysql练习题16- 当选者-杨大伟

需求：请编写 sql 语句来找到当选者（CandidateId）的名字

展示效果：

Name
B

 1 Create table If Not Exists Candidate (id int, Name varchar(255));
 2 Create table If Not Exists Vote (id int, CandidateId int);
 3 
 4 insert into Candidate (id, Name) values (1, 'A');
 5 insert into Candidate (id, Name) values (2, 'B');
 6 insert into Candidate (id, Name) values (3, 'C');
 7 insert into Candidate (id, Name) values (4, 'D');
 8 insert into Candidate (id, Name) values (5, 'E');
 9 
10 insert into Vote (id, CandidateId) values (1, 2);
11 insert into Vote (id, CandidateId) values (2, 44);
12 insert into Vote (id, CandidateId) values (3, 3);
13 insert into Vote (id, CandidateId) values (4, 2);
14 insert into Vote (id, CandidateId) values (5, 5);

最终SQL:

 1 SELECT
 2     name AS 'Name'
 3 FROM
 4     Candidate
 5 JOIN
 6     (SELECT
 7         Candidateid
 8     FROM
 9         Vote
10     GROUP BY 
11         Candidateid
12     ORDER BY 
13         COUNT(*) DESC
14     LIMIT 1
15     ) AS winner
16 WHERE
17     Candidate.id = winner.Candidateid;