大数据第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;
