表: 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
审题:查找被选最多的人的名字。
思考:根据表二,查找最多id,根据id查找名字。
解题:
解法一
按CandidateId分组,统计投票个数,取投票数最高的CandidateId。
再连接表Candidate,取姓名。
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)
##自己写
select C.name from Candidate as C join (
##根据表2分组,然后查询最多的id。
select V.CandidateId, count(V.id) as cnt
from Vote as V
##分组
group by V.CandidateId
##排序
limit 0,1
order by cnt desc
) on (C.id = A.candidateId)
解法二
直接连接两表,再按Candidate分组,统计投票数,取最高人。
有个重要的细节是Vote表中的某些CandidateId可能不在Candidate 表中。
因此,需要在外围过滤掉Name是NULL的行。
select A.Name
from(
select C.Name
from Vote as V left join
Candidate as C
on(C.id = V.CandidateId)
group by V.CandidateId,C.Name
order by count(V.id) desc
limit 0,1
) as A
where A.Name is not null
##自己写
select A.Name from (
select C.name from Vote As V left join
Candidate as C on (C.id = V.candidateId)
group by V.candidate, C.Name
order by count(V.id) desc
limit 0,1
) as A where A.name is not null;
知识点:
limit用法,选择第一个结果。
select * from tableName limit i,n # tableName:表名 # i:为查询结果的索引值(默认从0开始),当i=0时可省略i # n:为查询结果返回的数量 # i与n之间使用英文逗号","隔开 # limit n 等同于 limit 0,n