最近
目录
把很久不用的SQL拿出来温习一下，主要思路是以练代学，在练习中回忆以前学习的知识点。
学习内容来自于CSDN博主“报告，今天也有好好学习”两篇SQL的基础练习： 如何快速掌握MYSQL？附LeetCode上出现频率最高的50道数据库题目详解_老吴的博客-CSDN博客
如何快速掌握MYSQL？附LeetCode上出现频率最高的50道数据库题目详解_老吴的博客-CSDN博客
如何快速掌握MYSQL？附牛客网所有SQL题目详解_老吴的博客-CSDN博客
开始了哦
No.1 组合两个表
select FirstName,LastName,City,State
from Person
left join Address
on Person.PersonId=Address.PersonIdLEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
No.2 获得第二高的薪水
select ifNull ((
select distinct Salary
from Employee
order by Salary desc
limit 1,1),Null) as SecondhighestSalaryifNull(a,b): 如果a不等于Null，则返回a，否则返回b。是 if(X=Null,a,b)的简写
distinct:去重
limit:用于返回前几条或者中间某几行数据，(limit m,n) , m指从m+1条记录行开始检索，n代表取出n条数据
No.3 部门工资最高的员工
# 聚合函数与group by一起使用，会返回每组的聚合结果
# 聚合函数与group by直接使用的时候会出现问题，所以思路是先筛选出每组中最高的工资，再匹配数据
select b.Name Department, a.Name Employee, Salary
from Employee a, Department b
where a.DepartmentId=b.Id and
(a.DepartmentId,Salary) in (
select DepartmentId, max(Salary)
from Employee
group by DepartmentId )No. 4 找到第N高的薪资
#主要考察limit的使用
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n=n-1;
RETURN (
# Write your MySQL query statement below.
Select IfNull (( select Distinct Salary
from Employee
order by Salary desc
limit n,1),Null)
);
ENDNo.5 连续出现的数
# 注意连续出现至少3次的数字
select distinct a.num as ConsecutiveNums
from Logs as a, Logs as b, logs as c
where a.num=b.num and b.num=c.num and a.id=b.id-1 and b.id=c.id-1No.6 部门工资前三高的员工
# 学习使用窗口函数，发现解这种排序问题使用窗口函数是很好用的
# 利用窗口函数先分组排序，再进行选取，很简单的可以解决这类问题
Select d.Name Department, ranking.Name Employee, ranking.Salary
from (
select *,
dense_rank() over (partition by DepartmentId order by Salary desc ) as rk
from Employee) as ranking
join Department d on ranking.DepartmentId=d.Id
where rk<4推荐一篇讲窗口函数挺好的文章： 通俗易懂的学会：SQL窗口函数 - 知乎
No.7 每家商店的产品价格
# 注意要使用聚合函数对if的结果进行聚合，将多个结果聚合成一行
select product_id,
sum(if(store='store1',price,null)) store1
sum(if(store='store2',price,null)) store2
sum(if(store='store2',price,null)) store3
from Products
group by product_idNo.8 分数排名
# 窗口函数yyds
select Score,
dense_rank() over (order by Score desc) as 'Rank'
from Scores No.9 181. 超过经理收入的员工
#
select a.Name as Employee
from Employee a
left join Employee b on a.ManagerId=b.Id
where a.salary>b.salary No.10 262. 行程和用户
#join
select t.Request_at Day, Round(sum(If((Status='cancelled_by_driver' or Status='cancelled_by_client'),1,0))/count(t.Id),2) 'Cancellation Rate'
from Trips t
join Users u on t.Client_Id=u.Users_Id
join Users w on t.Driver_Id=w.Users_Id
where u.Banned!='Yes' and w.Banned!='Yes'
group by t.Request_atNo.11 体育馆的人流量
select distinct(s1.id) id, s1.visit_date, s1.people
from Stadium s1, Stadium s2, Stadium s3
where s1.people>=100 and s2.people>=100 and s3.people>=100
and ((s1.id=s2.id-1 and s2.id=s3.id-1) or (s1.id=s2.id+1 and s1.id=s3.id-1 ) or (s1.id=s2.id+1 and s1.id=s3.id+2))
order by s1.idNo.12 删除重复的电子邮箱
# 窗口函数还能这么使用
delete
from Person
where Id in (select id from (select id , row_number() over (partition by Email order by Id ) ranking from Person ) as a where ranking>1 )No.13 1179. 重新格式化部门表
# case when 和 if 都可以使用
# 要使用聚合函数
select id,
sum(case when month='Jan' then revenue else null end ) Jan_Revenue,
sum(case when month='Feb' then revenue else null end ) Feb_Revenue,
sum(case when month='Mar' then revenue else null end ) Mar_Revenue,
sum(case when month='Apr' then revenue else null end ) Apr_Revenue,
sum(case when month='May' then revenue else null end ) May_Revenue,
sum(case when month='Jun' then revenue else null end ) Jun_Revenue,
sum(case when month='Jul' then revenue else null end ) Jul_Revenue,
sum(case when month='Aug' then revenue else null end ) Aug_Revenue,
sum(case when month='Sep' then revenue else null end ) Sep_Revenue,
sum(case when month='Oct' then revenue else null end ) Oct_Revenue,
sum(case when month='Nov' then revenue else null end ) Nov_Revenue,
sum(case when month='Dec' then revenue else null end ) Dec_Revenue
from Department
group by id No.14 182. 查找重复的电子邮箱
select distinct Email
from (
select Email, row_number() over (partition by Email) rk
from Person ) a
where rk>1
##
select Email
from Person
group by Email
having count(Id)>1No.15 197. 上升的温度
select distinct w2.id
from Weather w1, Weather w2
where w1.recordDate=w2.recordDate-1 and w1.Temperature<w2.Temperature
##直接使用上述代码，逻辑上没有错，但是因为时间格式转换问题，测试用例有一个无法通过
#使用DATEDIFF就可以很好解决这个问题
select distinct w2.id
from Weather w1, Weather w2
where DATEDIFF (w1.recordDate,w2.recordDate)=-1 and w1.Temperature<w2.TemperatureNo.16 626. 换座位
#我的方法是换名字，解析中更简单的思路是换ID
select id, if(
(s1.id =(select max(id) from seat) and mod(id,2)=1),student,
if (
mod(id,2)=1,
(select student from seat s2 where s1.id=(s2.id-1) ),
(select student from seat s3 where s1.id=(s3.id+1)))
) as student
from seat s1No.17 620. 有趣的电影
select id,movie,description, rating
from cinema
where id%2!=0 and description!='boring'
order by rating descNo.18 627. 变更性别
update Salary
set sex=if (sex='m','f','m')No.19 183. 从不订购的客户
select Name as Customers
from Customers
where id not in (select CustomerId from Orders)No.20 511. 游戏玩法分析 I
select player_id, min(event_date) as first_login
from Activity
group by player_idNo.21 595. 大的国家
select name,population,area
from World
where area>=3000000 or population>=25000000No.22 1873. 计算特殊奖金
select employee_id, if(employee_id%2=0 or LEFT(name,1)='m',0,salary)as bonus
from EmployeeNo.23 1097. 游戏玩法分析 V
#筛选出哪些日期有用户安装了游戏很重要
select m as install_dt, count(distinct player_id) as installs, round(sum(if(datediff(event_date,m)=1),1,0)/count(distinct player_id)) as Day1_retention
from
(select player_id, event_date, min(event_date) over (partition by player_id) as m
from Activity) f
group by m
No.24 569. 员工薪水中位数
#米有leecode会员，没想到不使用任何内置函数的情况下解决的办法
# 窗口函数解决起来还是很简单的
select id, company salary
from (select *, row_number() over (partition by company order by salary) rk, count(company) over (partition by company) mi
from Employee ) a
where rk>=mi/2 and rk<=countmi/2+1No.25 1841. League Statistics
# 没有力扣会员，不好写，搬运博主的解法
SELECT
t.team_name,
COUNT(*) AS 'matches_played',
SUM(
CASE
WHEN (t.team_id=m.home_team_id AND m.home_team_goals>m.away_team_goals) OR
(t.team_id=m.away_team_id AND m.home_team_goals<m.away_team_goals) THEN 3
WHEN (t.team_id=m.home_team_id AND m.home_team_goals=m.away_team_goals) OR
(t.team_id=m.away_team_id AND m.home_team_goals=m.away_team_goals) THEN 1
WHEN (t.team_id=m.home_team_id AND m.home_team_goals<m.away_team_goals) OR
(t.team_id=m.away_team_id AND m.home_team_goals>m.away_team_goals) THEN 0
END
) AS 'points',
SUM(IF(t.team_id=m.home_team_id, m.home_team_goals, m.away_team_goals)) AS 'goal_for',
SUM(IF(t.team_id=m.home_team_id, m.away_team_goals, m.home_team_goals)) AS 'goal_against',
SUM(IF(t.team_id=m.home_team_id, m.home_team_goals, m.away_team_goals))-SUM(IF(t.team_id=m.home_team_id, m.away_team_goals, m.home_team_goals)) AS 'goal_diff'
FROM Teams t JOIN Matches m ON t.team_id=m.home_team_id OR t.team_id=m.away_team_id
GROUP BY team_id
ORDER BY points DESC, goal_diff DESC, team_name
No.26 571. 给定数字的频率查询中位数
# 有点问题，需要重新思考
select avg(number)
from (select *, sum(Frequence) over (order by number) a from number
(row_number() over (order by Number ) b ) s1
join
(select *, sum(Frequence) over (order by number) a from number ) s2
on s1.b=s2.number
where s1.a<=(count(s1.frequence)/2+1) and s2.a>=(count(s1.frequence)/2+1) No.27 618. 学生地理信息报告
#这题一开始确实没想法
# 核心思想是将最终生成表中的对应行数提前生成，方便后续按此来分组。MAX没有什么实际含义，只是可以用来聚合，MIN函数也行，而其他聚合函数如SUM函数的话会进行计数，所以不能用。
SELECT
MAX(IF(continent='America', name, NULL)) AS 'America',
MAX(IF(continent='Asia', name, NULL)) AS 'Asia',
MAX(IF(continent='Europe', name, NULL)) AS 'Europe'
FROM
(SELECT *, ROW_NUMBER() OVER(PARTITION BY continent ORDER BY name) AS rn FROM student) AS t
GROUP BY rn
No.28 1083. 销售分析 II
select distinct buyer_id
from sales
where buyer_id in
(select buyer_id
from Sales,Product
where Sales.product_id=Product.product_id and product_name='S8')
and buyer_id not in
(select buyer_id
from Sales,Product
where Sales.product_id=Product.product_id and product_name='iPhone')No.29 1205. 每月交易II
# 建立两张表，一张存放approved的订单信息，同时处理日期，另外一张存放被退单的订单信息，同时处理日期，使用union将两张表连接起来，存储在一张表中，再计算
SELECT
month,
country,
SUM(IF(state='approved', 1, 0)) AS approved_count,
SUM(IF(state='approved', amount, 0)) AS approved_amount,
SUM(IF(state='declined', 1, 0)) AS chargeback_count,
SUM(IF(state='declined', amount, 0)) AS chargeback_amount
FROM
(
SELECT id, country, amount, state, DATE_FORMAT(trans_date, '%Y-%m') AS month FROM Transactions
WHERE state<>'declined'
UNION
SELECT c.trans_id, t2.country, t2.amount, 'declined' as state, DATE_FORMAT(c.trans_date, '%Y-%m') AS month
FROM Chargebacks c JOIN Transactions t2 ON t2.id=c.trans_id
) AS t
GROUP BY month, countryNo.30 1501. 可以放心投资的国家
#使用having对分组的结果进行筛选，而不是用where语句，where语句无法与聚合函数一起使用。
#https://blog.csdn.net/bestforxu/article/details/51131234
#join on or 语句可用于生成需要对两列匹配的表
select name as country
from (SELECT id,co.name as name, duration
FROM Person p join Country co on left(p.phone_number,3)=co.country_code
JOIN Calls ca ON id=caller_id or id=callee_id ) s
group by name
having sum(duration)/count(id)>(select sum(duration)/count(caller_id) from Calls)No.311821. 寻找今年具有正收入的客户
select customer_id
from Customers
where year='2021' and revenue>0No.32 1204. 最后一个能进入电梯的人
# 将符合要求的队列倒排序，第一个人就是题目要求的最后进入的人，用limit函数求得
select s1.person_name
from (select person_name, turn,sum(weight)over(order by turn) w from Queue ) s1
where w<=1000
order by turn desc
limit 1
## 如果所有人都能进入的话，以下这段代码就有问题
select s1.person_name
from (select person_name, turn,sum(weight)over(order by turn) w from Queue ) s1
join (select turn,sum(weight) over (order by turn) as n from Queue ) s2
on s1.turn=s2.turn-1
where w<=1000 and s2.n>1000 No.33 1082. 销售分析 I
#学会了使用all函数
select seller_id
from Sales
group by seller_id
having sum(price)>=all(select sum(price) from sales group by seller_id)No.34 1098. 小众书籍
# left join 和 ifull 很重要
select s1.book_id,s1.name
from Books s1
left join Orders s2
on s1.book_id=s2.book_id and dispatch_date>='2018-06-23'
where s1.available_from<='2019-05-23'
group by s1.book_id
having ifnull(sum(quantity),0) < 10NO.35 1270. 向公司CEO汇报工作的所有人
select distinct employee_id
from Employees
where employee_id!=1 and manager_id in
(
select employee_id from employees where manager_id in
(select employee_id from employees where manager_id=1)
)No.36 597. 好友申请 I：总体通过率
#注意使用ifnull函数
#distinct A,B 可以实现多列去重
select round (
ifnull
(
(select count(distinct requester_id, accepter_id) from RequestAccepted) /
(select count(distinct sender_id,send_to_id) from FriendRequest)
,0),2
) as accept_rateNo.37 1193. 每月交易 I
select month,country,count(id) as trans_count, sum(if(state='approved',1,0)) as approved_count,sum(amount) as trans_total_amount, sum(if(state='approved',amount,0)) as approved_total_amount
from (select *, DATE_FORMAT(trans_date, '%Y-%m') AS month from Transactions ) s1
group by month,countryNo.38 1280. 学生们参加各科测试的次数
#用“cross join”笛卡尔积和left join， 实现每个学生的科目都存在
select s1.student_id,s1.student_name,s1.subject_name, ifnull(count(e.student_id),0) as attended_exams
from ( select *
from students
cross join subjects) s1
left join examinations e
on s1.student_id=e.student_id and s1.subject_name=e.subject_name
group by s1.student_id, s1.subject_name
order by s1.student_id,s1.subject_name
No.39 1715. 苹果和橘子的个数
#对小箱要使用ifnull，不然会报错
select (sum(b.apple_count)+sum(ifnull(c.apple_count,0)))as apple_count, (sum(b.orange_count) +sum(ifnull(c.orange_count,0)) )as orange_count
from Boxes b
left join Chests c on b.chest_id=c.chest_idNo.40 1809. 没有广告的剧集
select session_id
from playback
where session_id not in
(select session_id from playback p join Ads a on p.customer_id=a.customer_id and a.timestamp>=start_time and a.timestamp<=end_time)No.41 577. 员工奖金
select name,bonus
from employee e
left join bonus b on e.empid=b.empid
where ifnull(bonus,0)<1000 NO.42 603. 连续空余座位
#用abs简单
select distinct c1.seat_id
from cinema c1, cinema c2
where abs(c1.seat_id-c2.seat_id)=1 and c1.free=1 and c2.free=1
order by c1.seat_idNo.43 1112. 每位学生的最高成绩
#窗口函数比较简单
select student_id,course_id,grade
from (select *,row_number() over(partition by student_id order by grade desc, course_id) rk from enrollments) s1
where rk=1
group by student_idNo.44 1308. 不同性别每日分数总计
#窗口函数yyds
select gender,day,sum(score_points) over(partition by gender order by day) as total
from scores
order by gender,dayNo.45 570. 至少有5名直接下属的经理
select name
from employee
where id in (select managerId from Employee group by managerId having count(managerId)>=5)No.46 580. 统计各专业学生人数
#order 可以对select中的结果进行排序
select d.dept_name,ifnull(count(student_id),0) as student_number
from department d
left join student s
on d.dept_id=s.dept_id
group by d.dept_id
order by student_number desc, d.dept_nameNo.47 1407. 排名靠前的旅行者
select name, ifnull(sum(distance),0) as travelled_distance
from users
left join rides
on users.id=rides.user_id
group by rides.user_id
order by travelled_distance desc, nameNo.48 1795. 每个产品在不同商店的价格
select *
from
(
select product_id,'store1' as store,store1 as price from products
union
select product_id,'store2' as store,store2 as price from products
union
select product_id,'store3' as store,store3 as price from products
) s1
where price<>0 or store<>Null
order by storeNo.49 游戏玩法分析IV
select round (count(distinct player_id)/(select count(distinct player_id) from Activity),2) as fraction
from
(select *, rank() over(partition by player_id order by event_date) rk,
lead(event_date,1) over(partition by player_id order by event_date) nextday
from Activity) s1
where datediff(event_date, nextday)=-1 and rk=1No.50 579. 查询员工的累计薪水
#题意比较难理解，首先排除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）
#两段不连续的月份也要按以上规则计算
SELECT t1.Id,t1.Month, SUM(t2.Salary) Salary
FROM Employee t1 JOIN Employee t2
ON t1.Id=t2.Id AND t1.Month>=t2.Month AND t1.Month-t2.month<3
WHERE (t2.Id, t2.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id) AND (t1.Id, t1.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)
GROUP BY t1.Id,t1.Month
ORDER BY t1.Id, t1.Month DESC一周练完不快不慢吧，好久不编代码有点手生，练完50道之后就好很多了 。
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
