表: 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 |
+------+
注意:
你可以假设没有平局,换言之,最多只有一位当选者。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/winning-candidate
最原始的想法的代码:
# Write your MySQL query statement below
select
t3.name
from
(
select max(votes) as maxvotes
from
(select count(1)as votes from Vote group by CandidateId)pp
)t1
left join
(
select count(1)as votes,CandidateId from Vote group by CandidateId
)t2
on t1.maxvotes=t2.votes
left join Candidate t3
on t2.CandidateId=t3.id
看了下官方的题解,写的比我好得多
SELECT
name AS 'Name'
FROM
Candidate
JOIN
(SELECT
Candidateid
FROM
Vote
GROUP BY Candidateid
ORDER BY COUNT(*) DESC
LIMIT 1) AS winner
WHERE
Candidate.id = winner.Candidateid