Problem
Table: Candidate
1 2 3 4 5 6 7 8 9
|
+-----+---------+ | id | Name | +-----+---------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | +-----+---------+
|
Table: Vote
1 2 3 4 5 6 7 8 9 10 11
|
+-----+--------------+ | 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.
|
Write a sql to find the name of the winning candidate, the above example will return the winner B.
1 2 3 4 5
|
+------+ | Name | +------+ | B | +------+
|
Notes:
You may assume there is no tie, in other words there will be at most one winning candidate.
Analysis
Find the id of candidate who gets the most votes, then output the name.
First, find the CandidateId having the most votes in Vote table:
1
|
SELECT CandidateId FROM Vote GROUP BY CandidateId ORDER BY COUNT(*) DESC LIMIT 1;
|
Output Name of the candidate whose id is the CandidateId with most votes:
1 2
|
SELECT Name FROM Candidate WHERE id = (SELECT CandidateId FROM Vote GROUP BY CandidateId ORDER BY COUNT(*) DESC LIMIT 1);
|
Solution
1 2
|
SELECT Name FROM Candidate WHERE id = (SELECT CandidateId FROM Vote GROUP BY CandidateId ORDER BY COUNT(*) DESC LIMIT 1);
|
Link
574. Winning Candidate
(中文版) SQL 笔记: Leetcode#574 Winning Candidate
近期评论