98，SQL训练之，力扣，574. 当选者

最新推荐文章于 2024-10-05 12:29:44 发布

凡梦_leo

最新推荐文章于 2024-10-05 12:29:44 发布

阅读量718

点赞数 13

分类专栏： SQL实战训练文章标签： leetcode 算法 sql 开窗函数排序 pandas 数据库

本文链接：https://blog.csdn.net/qq_55006020/article/details/142503123

版权

SQL实战训练专栏收录该内容

120 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表: Candidate

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
+-------------+----------+
id 是该表中具有唯一值的列
该表的每一行都包含关于候选对象的id和名称的信息。

表: Vote

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| candidateId | int  |
+-------------+------+
id 是自动递增的主键(具有唯一值的列)。
candidateId是id来自Candidate表的外键(reference 列)。
该表的每一行决定了在选举中获得第i张选票的候选人。

编写解决方案来报告获胜候选人的名字(即获得最多选票的候选人)。

生成的测试用例保证 只有一个候选人赢得 选举。

返回结果格式如下所示。

示例 1:

输入: 
Candidate table:
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
| 4  | D    |
| 5  | E    |
+----+------+
Vote table:
+----+-------------+
| id | candidateId |
+----+-------------+
| 1  | 2           |
| 2  | 4           |
| 3  | 3           |
| 4  | 2           |
| 5  | 5           |
+----+-------------+
输出: 
+------+
| name |
+------+
| B    |
+------+
解释: 
候选人B有2票。候选人C、D、E各有1票。
获胜者是候选人B。

三，建表语句


Create table If Not Exists Candidate (id int, name varchar(255));
Create table If Not Exists Vote (id int, candidateId int);
Truncate table Candidate;
insert into Candidate (id, name) values ('1', 'A');
insert into Candidate (id, name) values ('2', 'B');
insert into Candidate (id, name) values ('3', 'C');
insert into Candidate (id, name) values ('4', 'D');
insert into Candidate (id, name) values ('5', 'E');
Truncate table Vote;
insert into Vote (id, candidateId) values ('1', '2');
insert into Vote (id, candidateId) values ('2', '4');
insert into Vote (id, candidateId) values ('3', '3');
insert into Vote (id, candidateId) values ('4', '2');
insert into Vote (id, candidateId) values ('5', '5');

select * from candidate;
select * from vote;

四，分析

题解：

表1：候选人表

字段：候选人id，候选人姓名

表2：候选人投标表

字段。id，候选人id

求投票最多的候选人

第一步，左连接2个表

with t1 as (
    select c.id,c.name,v.candidateId from candidate c left join vote v on c.id=v.candidateId
)
select * from t1;

第二步：以姓名分组，统计得票数

#解法一
with t1 as (
    select c.id,c.name,v.candidateId from candidate c left join vote v on c.id=v.candidateId
)
# select * from t1;
, t2 as (
    select
    name,count(name) cnt
from t1 group by name
)
   select * from t2;

第三步：用max函数求cnt 开窗最大的数


#解法一
with t1 as (
    select c.id,c.name,v.candidateId from candidate c left join vote v on c.id=v.candidateId
)
# select * from t1;
, t2 as (
    select
    name,count(name) cnt
from t1 group by name
)
#    select * from t2;
   ,t3 as (
    select name,cnt,max(cnt)over() mo from t2
)
select * from t3;

最后一步投票的次数等于最大数就取他的姓名

或者用子查询也行最后一步的时候：

五，SQL解答

#解法一
with t1 as (
    select c.id,c.name,v.candidateId from candidate c left join vote v on c.id=v.candidateId
)
, t2 as (
    select
    name,count(name) cnt
from t1 group by name
)
   ,t3 as (
    select name,cnt,max(cnt)over() mo from t2
)
select name from t3 where cnt=mo;

#解法二
with t1 as (
    select c.id,c.name,v.candidateId from candidate c left join vote v on c.id=v.candidateId
),t2 as (
    select
    name,count(name) cnt
from t1 group by name
)
select name from t2 where cnt =(select max(cnt) from t2);

用分组求top1的方式求：


with t1 as (
    select c.id, c.name, v.candidateid
    from candidate c left join vote v on c.id = v.candidateid
),
t2 as (
    select name, count(name) as cnt,
           row_number() over (order by count(name) desc) as rn
    from t1
    group by name
)
select * from t2;