陕西省建设执业资格注册中心网站,wordpress英文变中文,硅胶科技东莞网站建设,百度网站置顶怎么做力扣sql中等篇练习(二十)
1 寻找面试候选人
1.1 题目内容
1.1.1 基本题目信息1 1.1.2 基本题目信息2 1.1.3 示例输入输出
a 示例输入 b 示例输出 1.2 示例sql语句
# 分为以下两者情况,分别考虑,然后union进行处理(有可能同时满足,需要去进行去重)
# ①该用户在 三场及更多…力扣sql中等篇练习(二十)
1 寻找面试候选人
1.1 题目内容
1.1.1 基本题目信息1 1.1.2 基本题目信息2 1.1.3 示例输入输出
a 示例输入 b 示例输出 1.2 示例sql语句
# 分为以下两者情况,分别考虑,然后union进行处理(有可能同时满足,需要去进行去重)
# ①该用户在 三场及更多不同的 比赛中赢得 金牌这些比赛可以不是连续的分组然后数量大于3即可
#连续三年以及更多,意味着只需要满足三年的情况就行
#使用with创建临时表(查出每年用户是否获奖),inner join后面可以直接接上union
WITH T AS
(SELECT u.user_id,c.contest_idFROM Contests cINNER JOIN Users uON c.gold_medalu.user_idOR c.silver_medalu.user_idOR c.bronze_medalu.user_idORDER BY u.user_id,c.contest_id
)
SELECT name,mail
FROM Users
WHERE user_id IN
(SELECT distinct t1.user_idFROM T t1INNER JOIN T t2ON t1.user_idt2.user_id AND t1.contest_id1t2.contest_idINNER JOIN T t3ON t2.user_idt3.user_id AND t2.contest_id1t3.contest_idUNIONSELECT gold_medal user_idFROM ContestsGROUP BY gold_medalHAVING count(contest_id)3
)1.3 运行截图 2 每天的最大交易
2.1 题目内容
2.1.1 基本题目信息 2.1.2 示例输入输出 2.2 示例sql语句
# Write your MySQL query statement below
SELECT t1.transaction_id
FROM
(SELECT transaction_id,date_format(day,%Y-%m-%d) d,amountFROM Transactions
)t1
INNER JOIN
(SELECT t2.d,MAX(t2.amount) nFROM(SELECT transaction_id,date_format(day,%Y-%m-%d) d,amountFROM Transactions )t2GROUP BY t2.d
)t3
ON t1.dt3.d AND t1.amountt3.n
ORDER BY t1.transaction_id asc2.3 运行截图 3 联赛信息统计
3.1 题目内容
3.1.1 基本题目信息1 3.1.2 基本题目信息2 3.1.3 示例输入输出 3.2 示例sql语句
# 注意表中是进球数,不是得分数
# 分别求出客场时得分和主场时得分,两者合并后再分组即可
# 按照字典顺序默认就是升序
SELECT (SELECT team_name FROM Teams WHERE team_idt1.team_id) team_name,count(*) matches_played,sum(t1.score) points,SUM(t1.n1) goal_for,SUM(t1.n2) goal_against,SUM(t1.n1)-SUM(t1.n2) goal_diff
FROM
(SELECT home_team_id team_id,IF(home_team_goalsaway_team_goals,3,IF(home_team_goalsaway_team_goals,1,0)) score,home_team_goals n1,away_team_goals n2FROM MatchesUNION ALLSELECT away_team_id team_id,IF(home_team_goalsaway_team_goals,3,IF(home_team_goalsaway_team_goals,1,0)) score,away_team_goals n1,home_team_goals n2FROM Matches
)t1
GROUP BY t1.team_id
ORDER BY points desc,goal_diff desc,team_name asc3.3 运行截图
