本文主要是介绍MySQL高阶练习题1- 寻找面试候选人,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
目录
题目
准备数据
分析数据
实现代码
总结
题目
返回 所有面试候选人 的姓名 name
和邮件 mail
。当用户满足以下两个要求中的 任意一条 ,其成为 面试候选人 :
- 该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
- 该用户在 三场及更多不同的 比赛中赢得 金牌(这些比赛可以不是连续的)
准备数据
## 创建库
create database db;
use db;## 创建Contests表
Create table If Not Exists Contests (contest_id int, gold_medal int, silver_medal int, bronze_medal int);## 创建Users表
Create table If Not Exists Users (user_id int, mail varchar(50), name varchar(30));## 向表中插入数据
Truncate table Contests;
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('190', '1', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('191', '2', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('192', '5', '2', '3');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('193', '1', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('194', '4', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('195', '4', '2', '1');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('196', '1', '5', '2');
Truncate table Users;
insert into Users (user_id, mail, name) values ('1', 'sarah@leetcode.com', 'Sarah');
insert into Users (user_id, mail, name) values ('2', 'bob@leetcode.com', 'Bob');
insert into Users (user_id, mail, name) values ('3', 'alice@leetcode.com', 'Alice');
insert into Users (user_id, mail, name) values ('4', 'hercy@leetcode.com', 'Hercy');
insert into Users (user_id, mail, name) values ('5', 'quarz@leetcode.com', 'Quarz');
输入表
contests表
users表
分析数据
分析一:为了容易连接,将金牌,银牌,铜牌设置成一列。
select contest_id,'gold' type,gold_medal user_id from contestsunion allselect contest_id,'silver' type,silver_medal user_id from contestsunion allselect contest_id,'bronze' type,bronze_medal user_id from contests
分析二:该用户连续三场及更多比赛中赢得任意奖牌
with t as (select contest_id,'gold' type,gold_medal user_id from contestsunion allselect contest_id,'silver' type,silver_medal user_id from contestsunion allselect contest_id,'bronze' type,bronze_medal user_id from contests
), t1 as (select t1.user_id from t t1,t t2,t t3where t1.user_id = t2.user_id and t2.user_id = t3.user_idand t1.contest_id + 1 = t2.contest_id and t2.contest_id + 1 = t3.contest_id)
select * from t1;
分析三: 该用户在三场及更多不同的比赛中赢得金牌(可不连续)
with t as (select contest_id,'gold' type,gold_medal user_id from contestsunion allselect contest_id,'silver' type,silver_medal user_id from contestsunion allselect contest_id,'bronze' type,bronze_medal user_id from contests
)select user_id from t where type = 'gold' group by user_id having count(*) >= 3;
实现代码
with t as (select contest_id,'gold' type,gold_medal user_id from contestsunion allselect contest_id,'silver' type,silver_medal user_id from contestsunion allselect contest_id,'bronze' type,bronze_medal user_id from contests
), t1 as (select t1.user_id from t t1,t t2,t t3where t1.user_id = t2.user_id and t2.user_id = t3.user_idand t1.contest_id + 1 = t2.contest_id and t2.contest_id + 1 = t3.contest_id), t2 as (select user_id from t where type = 'gold' group by user_id having count(*) >= 3
), t3 as (select user_id from t1 union select user_id from t2
)
select u.name,u.mail from t3 , users u where t3.user_id = u.user_id;
总结
-
如果一张表中出现多个列可以与另一张表进行关联,可以把多个列利用union all连接成一个列,最后进行关联条件。
-
出现两个互不关联的条件,可以分别求出,最后再利用union 进行连接
-
union或者union all是多个select语句进行连接的,且每个select语句字段个数和顺序需一致
这篇关于MySQL高阶练习题1- 寻找面试候选人的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!