- 刷题网址：https://www.nowcoder.com/exam/oj?questionJobId=10&subTabName=online_coding_page
- 有时主页显示的题目序号与点进去之后的题目序号有所不同，这里以点进去之后的题目序号为主，如果日后还是有所出入，可以凭题目名称找相应答案。
- 每一道题目，都是博主认真做完后，又根据题解部分的高赞评论优化后的最优解，适合刷题时懒得看题解的朋友们，做完题只需直接与这里的答案比对即可，不用担心错过最优解。
- 本篇博文将会提供牛客所有SQL题目的最优解答，部分复杂题会提供简单讲解。
- 刷题快捷键：Ctrl + 左箭头：查看上一题；Ctrl + 右箭头：查看下一题
目录
一、SQL 必知必会（非常基础）
--SQL60：从 Customers 表中检索所有的 ID
select cust_id from Customers
--SQL61：检索并列出已订购产品的清单
select distinct prod_id from OrderItems
--SQL62：检索所有列
select * from Customers
--SQL63：检索顾客名称并且排序
select cust_name from Customers order by 1 desc
--SQL64：对顾客ID和日期排序
select cust_id, order_num from Orders
order by 1, order_date desc
--SQL65：按照数量和价格排序
select quantity, item_price from OrderItems
order by 1 desc, 2 desc
--SQL66：检查SQL语句
select vend_name from Vendors order by 1 desc
--SQL67：返回固定价格的产品
select prod_id, prod_name from Products where prod_price = 9.49
--SQL68：返回更高价格的产品
select prod_id, prod_name from Products where prod_price >= 9
--SQL69：返回产品并且按照价格排序
select prod_name, prod_price from Products
where prod_price between 3 and 6
order by 2
--SQL70：返回更多的产品
select order_num from OrderItems
group by order_num
having sum(quantity) >= 100
--SQL71：检索供应商名称
select vend_name from Vendors
where (vend_country, vend_state) = ('USA', 'CA')
--SQL72：检索并列出已订购产品的清单
select order_num, prod_id, quantity from OrderItems
where prod_id in ('BR01', 'BR02', 'BR03')
and quantity >= 100
--SQL73：返回所有价格在 3美元到 6美元之间的产品的名称和价格
select prod_name, prod_price from Products
where prod_price between 3 and 6
order by 2
--SQL74：纠错2
SELECT vend_name FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name
--SQL75：检索产品名称和描述（一）
select prod_name, prod_desc from Products
where prod_desc like '%toy%'
--SQL76：检索产品名称和描述（二）
select prod_name, prod_desc from Products
where prod_desc not like '%toy%'
-- 或者：where instr(prod_desc, 'toy') = 0
-- 或者：where locate('toy', prod_desc) = 0
order by 1
--SQL77：检索产品名称和描述（三）
select prod_name, prod_desc from Products
where prod_desc like '%toy%'
and prod_desc like '%carrots%'
--SQL78：检索产品名称和描述（四）
select prod_name, prod_desc from Products
where prod_desc like '%toy%carrots%'
--SQL79：别名
select vend_id, vend_name as vname,
vend_address as vaddress, vend_city as vcity
from Vendors
order by 2
--SQL80：打折
select prod_id, prod_price, prod_price * 0.9 as sale_price
from Products
--SQL81：顾客登录名
select cust_id, cust_name,
upper(concat(left(cust_contact, 2), left(cust_city, 3))) as user_login
from Customers
-- 或者：
select cust_id, cust_name,
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers
--SQL82：返回 2020 年 1 月的所有订单的订单号和订单日期
select order_num, order_date from Orders
where date_format(order_date, '%Y-%m') = '2020-01'
order by order_date
--SQL83：确定已售出产品的总数
select sum(quantity) as items_ordered from OrderItems
--SQL84：确定已售出产品项 BR01 的总数
select sum(quantity) as items_ordered from OrderItems
where prod_id ='BR01'
--SQL85：确定 Products 表中价格不超过 10 美元的最贵产品的价格
select max(prod_price) as max_price from Products where prod_price <= 10
--SQL86：返回每个订单号各有多少行数
select order_num, count(*) as order_lines from OrderItems
group by order_num
order by 2
--SQL87：每个供应商成本最低的产品
select vend_id, min(prod_price) as cheapest_item from Products
group by vend_id
order by 2
--SQL88：返回订单数量总和不小于100的所有订单的订单号
select order_num from OrderItems
group by order_num
having sum(quantity) >= 100
order by 1
--SQL89：计算总和
select order_num, sum(item_price * quantity) as total_price
from OrderItems
group by order_num
having total_price >= 1000
order by 1
--SQL90：纠错3
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING items >= 3
ORDER BY items, 1
--SQL91：返回购买价格为 10 美元或以上产品的顾客列表
select distinct cust_id from Orders
where order_num in (
select order_num from OrderItems where item_price >= 10
)
--SQL92：确定哪些订单购买了 prod_id 为 BR01 的产品（一）
-- SQL92 和 SQL98 是同一个问题
-- SQL92 要求采用子查询
-- SQL98 要求采用join联结语法、简单的等联结语法
-- 采用简单的等联结的解法：更优
select cust_id, order_date from Orders od, OrderItems oi
where od.order_num = oi.order_num and prod_id = "BR01"
order by 2
-- 采用join联结的解法：
select cust_id, order_date
from Orders od
join OrderItems oi
on od.order_num = oi.order_num and prod_id = "BR01"
order by 2
-- 采用子查询的解法：
select cust_id, order_date from Orders
where order_num in (
select order_num from OrderItems where prod_id = "BR01"
)
order by 2
--SQL93：返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）
-- SQL93 和 SQL99 是同一个问题
-- SQL93 要求采用子查询
-- SQL99 要求采用join联结语法
-- 采用join联结的解法: 更优
select cust_email from Orders od
join OrderItems oi on oi.order_num = od.order_num and prod_id = "BR01"
join Customers c on od.cust_id = c.cust_id
-- 采用子查询的解法：
select cust_email from Customers where cust_id in (
select cust_id from Orders where order_num in (
select order_num from OrderItems where prod_id = "BR01"
))
--SQL94：返回每个顾客不同订单的总金额
-- 解1：
select cust_id, (
select sum(item_price * quantity) from OrderItems oi where oi.order_num = od.order_num
) as total_ordered
from Orders od
order by 2 desc
-- 解2：
select cust_id, total_ordered
from Orders o, (
select order_num, sum(item_price * quantity) as total_ordered from OrderItems
group by order_num
) s
where s.order_num = o.order_num
order by 2 desc
--SQL95：从 Products 表中检索所有的产品名称以及对应的销售总数
-- 与 SQL94 的解1是同一种解法
select prod_name, (select sum(quantity) from OrderItems o where o.prod_id = p.prod_id) as quant_sold
from Products p
--SQL96：返回顾客名称和相关订单号
-- 解1：使用简单的等联结语法
select cust_name, order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by 1, 2
-- 解2：使用 INNER JOIN（可简写为 join）
select cust_name, order_num
from Customers c
join Orders o on o.cust_id = c.cust_id
order by 1, 2;
--SQL97：返回顾客名称和相关订单号以及每个订单的总价
-- 解1：使用简单的等联结语法
select cust_name, od.order_num, (quantity * item_price) as OrderTotal
from Customers c, Orders od, OrderItems oi
where c.cust_id = od.cust_id
and od.order_num = oi.order_num
order by 1, 2
-- 解2：使用 JOIN 连接法
select cust_name, od.order_num, (quantity * item_price) as OrderTotal
from Customers c
join Orders od on c.cust_id = od.cust_id
join OrderItems oi on od.order_num = oi.order_num
order by 1, 2
--SQL98：确定哪些订单购买了 prod_id 为 BR01 的产品（二）
-- SQL98 和 SQL92 是同一个问题
-- SQL92 要求采用子查询
-- SQL98 要求采用join联结语法、简单的等联结语法
-- 采用简单的等联结的解法：更优
select cust_id, order_date from Orders od, OrderItems oi
where od.order_num = oi.order_num and prod_id = "BR01"
order by 2
-- 采用join联结的解法：
select cust_id, order_date
from Orders od
join OrderItems oi
on od.order_num = oi.order_num and prod_id = "BR01"
order by 2
-- 采用子查询的解法：
select cust_id, order_date from Orders
where order_num in (
select order_num from OrderItems where prod_id = "BR01"
)
order by 2
--SQL99：返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）
-- SQL99 和 SQL93 是同一个问题
-- SQL93 要求采用子查询
-- SQL99 要求采用join联结语法
-- 采用join联结的解法: 更优
select cust_email from Orders od
join OrderItems oi on oi.order_num = od.order_num and prod_id = "BR01"
join Customers c on od.cust_id = c.cust_id
-- 采用子查询的解法：
select cust_email from Customers where cust_id in (
select cust_id from Orders where order_num in (
select order_num from OrderItems where prod_id = "BR01"
))
--SQL100：确定最佳顾客的另一种方式（二）
-- 解1：根据题目要求采用join连接的解法
select cust_name, sum(item_price * quantity) as total_price
from OrderItems oi
join Orders o on o.order_num = oi.order_num
join Customers c on c.cust_id = o.cust_id
group by 1
having total_price >= 1000
order by 1
-- 解2：采用简单的等联结的解法
select cust_name, sum(item_price * quantity) as total_price
from OrderItems oi, Orders o, Customers c
where o.order_num = oi.order_num and c.cust_id = o.cust_id
group by 1
having total_price >= 1000
order by 1
--SQL101：检索每个顾客的名称和所有的订单号（一）
--SQL102：检索每个顾客的名称和所有的订单号（二）
--SQL103：返回产品名称和与之相关的订单号
--SQL104：返回产品名称和每一项产品的总订单数
--SQL105：列出供应商及其可供产品的数量
--SQL106：将两个 SELECT 语句结合起来（一）
--SQL107：将两个 SELECT 语句结合起来（二）
--SQL108：组合 Products 表中的产品名称和 Customers 表中的顾客名称
二、SQL快速入门（实用全面）
--SQL1：查询所有列
select id, device_id, gender, age, university, province
from user_profile
--SQL2：查询多列
select device_id, gender, age, university
from user_profile
--SQL3：查询结果去重
select distinct university
from user_profile
--SQL4：查询结果限制返回行数
select device_id
from user_profile
limit 2
--SQL5：将查询后的列重新命名
select device_id as user_infos_example
from user_profile
limit 2
--SQL6：查找学校是北大的学生信息
select device_id, university
from user_profile
where university = '北京大学'
--SQL7: 查找年龄大于24岁的用户信息
select device_id, gender, age, university
from user_profile
where age > 24
--SQL8：查找某个年龄段的用户信息
select device_id, gender, age
from user_profile
where age between 20 and 23
--SQL9：查找除复旦大学的用户信息
select device_id, gender, age, university
from user_profile
where university != '复旦大学'
--SQL10：用where过滤空值练习
select device_id, gender, age, university
from user_profile
where age is not null and age <> ""
--SQL11: 高级操作符练习(1)
select device_id, gender, age, university, gpa
from user_profile
where gender = 'male'
and gpa > 3.5
--SQL12: 高级操作符练习(2)
select device_id, gender, age, university, gpa
from user_profile
where university = '北京大学'
or gpa > 3.7
--SQL13：Where in 和Not in
select device_id, gender, age, university, gpa
from user_profile
where university in ('北京大学', '复旦大学', '山东大学')
--SQL14：操作符混合运用
select device_id, gender, age, university, gpa
from user_profile
where gpa > 3.5
and university = '山东大学'
or gpa > 3.8
and university = '复旦大学'
order by device_id
--SQL15：查看学校名称中含北京的用户
select device_id, age, university
from user_profile
where university like '%北京%'
--SQL16: 查找GPA最高值
select max(gpa) as gpa
from user_profile
where university = '复旦大学'
--SQL17: 计算男生人数以及平均GPA
select count(gender) as male_num, round(avg(gpa), 1) as avg_gpa
from user_profile
where gender = 'male'
--SQL18: 分组计算练习题
select gender, university,
count(gender) as user_num,
round(avg(active_days_within_30), 1) as avg_active_day,
round(avg(question_cnt), 1) as avg_question_cnt
from user_profile
group by gender, university
order by gender, university
--SQL19: 分组过滤练习题
--解1：
select
university,
round(avg(question_cnt), 3) as avg_question_cnt,
round(avg(answer_cnt), 3) as avg_answer_cnt
from
user_profile
group by
university
having avg(question_cnt) < 5
or avg(answer_cnt) < 20
--解2：
select
university,
round(avg(question_cnt), 3) as avg_question_cnt,
round(avg(answer_cnt), 3) as avg_answer_cnt
from
user_profile
group by
university
having avg_question_cnt < 5
or avg_answer_cnt < 20
--SQL20: 分组排序练习题
--解1：
select
university,
round(avg(question_cnt), 4) as avg_question_cnt
from
user_profile
group by
university
order by avg(question_cnt)
--解2：
select
university,
round(avg(question_cnt), 4) as avg_question_cnt
from
user_profile
group by
university
order by avg_question_cnt
--SQL21：浙江大学用户题目回答情况
--解1：
select device_id, question_id, result
from question_practice_detail
where device_id in (
select distinct device_id
from user_profile
where university = '浙江大学'
)
--解2：
select q.device_id, q.question_id, q.result
from question_practice_detail q
join user_profile u
on q.device_id = u.device_id
where u.university = '浙江大学'
--SQL22：统计每个学校的答过题的用户的平均答题数
-- 去重后计数：count(distinct …)
select u.university, count(q.question_id)/count(distinct q.device_id) avg_answer_cnt
from question_practice_detail q
join user_profile u
on q.device_id = u.device_id
group by university
--SQL23：统计每个学校各难度的用户平均刷题数
--解1：
select u.university, q2.difficult_level,
count(q1.question_id)/count(distinct q1.device_id) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id = q1.device_id
join question_detail q2
on q1.question_id = q2.question_id
group by u.university, q2.difficult_level
order by u.university
--解2：隐式连接的写法
select u.university, q2.difficult_level,
count(q1.question_id)/count(distinct q1.device_id) as avg_answer_cnt
from user_profile u, question_practice_detail q1, question_detail q2
where u.device_id = q1.device_id
and q1.question_id = q2.question_id
group by u.university, q2.difficult_level
order by u.university
--SQL24：统计每个用户的平均刷题数
select u.university, q2.difficult_level,
count(q1.question_id)/count(distinct q1.device_id) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id = q1.device_id and u.university = '山东大学'
join question_detail q2
on q1.question_id = q2.question_id
group by u.university, q2.difficult_level
--SQL25：查找山东大学或者性别为男生的信息
-- 题目要求结果不去重，用 where or 或者 union 都会自动去重
-- 用 UNION ALL 可以保留重复行
select device_id, gender, age, gpa
from user_profile
where university = '山东大学'
union all
select device_id, gender, age, gpa
from user_profile
where gender = 'male'
--SQL26：计算25岁以上和以下的用户数量
--解1：case 语句是最优解法，很简洁
select
case
when ifnull(age, 0) < 25 then '25岁以下' -- 或者：when isnull(age) or age < 25 then '25岁以下'
when age >= 25 then '25岁及以上'
end age_cut,
count(device_id)
from user_profile
group by age_cut
--解2：纯属练习一下 union all
select '25岁以下' as age_cut, count(device_id) as number
from user_profile
where ifnull(age, 0) < 25
union all
select '25岁及以上' as age_cut, count(device_id) as number
from user_profile
where age >= 25
--SQL27：查看不同年龄段的用户明细
select device_id, gender,
case
when age >= 25 then '25岁及以上'
when age between 20 and 24 then '20-24岁'
when age < 20 then '20岁以下'
else '其他'
end age_cut
from user_profile
--SQL28：计算用户8月每天的练题数量
select day(date) as day,
count(question_id) as question_cnt
from question_practice_detail
where year(date) = 2021 -- 或者：where date_format(date, '%Y%m') = '202108'
and month(date) = 8
group by day
--SQL29：计算用户的平均次日留存率
-- 知识点：count 函数参数里面的 distinct 也是不限字段个数的
select (
select count(distinct q1.device_id, q1.date)
from question_practice_detail q1
join question_practice_detail q2
on date_add(q1.date, interval 1 day) = q2.date
and q1.device_id = q2.device_id
) / count(distinct device_id, date) as avg_ret
from question_practice_detail
--SQL30：统计每种性别的人数
/*
-- SUBSTRING_INDEX：截取字符串的函数，它根据指定的分隔符将字符串分割，并返回指定部分。
SUBSTRING_INDEX(str, delimiter, count)
-- str：待分割的字符串。
-- delimiter：分隔符（如 ,、.、/ 等）。
-- count：正数：从左到右截取，返回前 count 个分隔符左侧的所有内容。负数：从右到左截取，返回后 count 个分隔符右侧的所有内容。
SELECT SUBSTRING_INDEX('www.example.com', '.', 2); -- 输出：www.example
select substring_index('180cm,75kg,27,male', ',', -1); -- 输出：male
*/
-- 解：
select
substring_index(profile, ',', -1) as gender,
count(device_id) as number
from user_submit
group by gender
--SQL31：提取博客URL中的用户名
-- 再次练习substring_index函数的用法
select device_id,
substring_index(blog_url, '/url/', -1) as user_name
from user_submit
--SQL32：截取出年龄
-- 这已经是最简洁的写法了
select
substring_index(substring_index(profile, ',', -2), ',', 1) as age,
count(device_id) as number
from user_submit
group by age
--SQL33：找出每个学校GPA最低的同学
--解1：个人认为窗口函数最稳妥
select device_id, university, gpa
from (
select device_id, university, gpa,
rank()over(partition by university order by gpa) as r
from user_profile
) s
where s.r = 1
--解2：
select device_id, university, gpa
from user_profile
where (university, gpa) in (
select university, min(gpa)
from user_profile
group by university
)
order by university
--SQL34：统计复旦用户8月练题情况
--解1：LEFT JOIN 保留左表的所有行，即使右表无匹配项。若ON条件因NULL值无法判断，左表行仍会保留。这就是为什么month(q.date) = 8条件放在on后面可以，而放在where后面却不可以的原因
select u.device_id, u.university,
count(q.question_id) as question_cnt,
sum(if(q.result = 'right', 1, 0)) as right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
and month(q.date) = 8
where u.university = '复旦大学'
group by u.device_id, u.university
--解2：条件放在where后面的解法
select u.device_id, u.university,
count(q.question_id) as question_cnt,
sum(if(q.result = 'right', 1, 0)) as right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
where u.university = '复旦大学'
and (month(q.date) = 8 or q.date <=> null)
group by u.device_id, u.university
--SQL35：浙大不同难度题目的正确率
select difficult_level,
avg(if(q1.result = 'right', 1, 0)) as correct_rate
-- 或者：sum(if(q.result = 'right', 1, 0)) as right_question_cnt
-- 或者：count(if(q1.result = 'right', 1, null))/count(q1.question_id) as correct_rate
from user_profile u
join question_practice_detail q1
on q1.device_id = u.device_id
join question_detail q2
on q2.question_id = q1.question_id
where u.university = '浙江大学'
group by difficult_level
order by correct_rate
--SQL36：查找后排序
select device_id, age
from user_profile
order by age
--SQL37：查找后多列排序
select device_id, gpa, age
from user_profile
order by gpa, age
--SQL38：查找后降序排列
select device_id, gpa, age
from user_profile
order by gpa desc, age desc
--SQL39：21年8月份练题总数
select
count(distinct device_id) did_cnt,
count(question_id) question_cnt
from question_practice_detail
where date_format(date, '%Y%m') = '202108'
--SQL40：电话号码格式校验
/*
/*
^ 表示字符串开始
$表示字符串结束
[]表示 character set，结合-使用表示范围，eg: [1-9]表示1,2,3,4,5....8,9组成的集合
{}为数量符，eg:[0-9]{2}表示搜寻'2个0-9中的任意字符'
?为数量符合，表示0或1个, eg -?表示搜寻'0个或者1个字符 "-"
^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4} 匹配开头，但允许字符串后续包含其他字符（如 123-456-7890abc 会匹配）。
^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$ 严格匹配整个字符串，不允许后续有其他字符。
*/
--解1：
select id, name, phone_number
from contacts
where phone_number regexp '^[1-9][0-9]{2}-?[0-9]{3}-?[0-9]{4}$'
order by id
--解2：不记得“?”使用方法的情况下的解法
select id, name, phone_number
from contacts
where phone_number regexp '^[1-9][0-9]{9}$'
or phone_number regexp '^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$'
order by id
--SQL41：计算每日累计利润
-- SUM(), AVG(), MIN(), MAX(), COUNT() 后接 over() 可实现聚合类窗口函数
select profit_id, profit_date, profit,
sum(profit)over(order by profit_date) as cumulative_profit
from daily_profits
order by profit_date
--SQL42：基本数学函数
select id, value,
abs(value) as absolute_value, -- 取绝对值
ceil(value) as ceiling_value, -- 向上取整
floor(value) as floor_value, -- 向下取整
round(value, 1) as rounded_value -- 四舍五入到一位小数
from numbers
order by id三、SQL 热题（开始涉及增删改操作，复杂题较多）
--SQL200：查找最晚入职员工的所有信息
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees
where hire_date = (
select max(hire_date) from employees
)
--SQL201：查找入职员工时间排名倒数第三的员工所有信息
--解1：用窗口函数
select emp_no, birth_date, first_name, last_name, gender, hire_date
from (
select emp_no, birth_date, first_name, last_name, gender, hire_date,
dense_rank()over(order by hire_date desc) as r
from employees
) e
where e.r = 3
order by emp_no
--解2：
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees
where hire_date = (
select distinct hire_date from employees
order by hire_date desc
limit 2, 1 # 或者：LIMIT 1 OFFSET 2
)
order by emp_no
--SQL202：查找当前薪水详情以及部门编号dept_no
select d.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
from dept_manager d
left join salaries s
on d.emp_no = s.emp_no
--SQL203：查找所有已经分配部门的员工的last_name和first_name以及dept_no
select last_name, first_name, dept_no
from employees e
join dept_emp d
on e.emp_no = d.emp_no
--SQL204：查找所有员工的last_name和first_name以及对应部门编号dept_no
select last_name, first_name, dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
--SQL206：查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t
select emp_no, count(salary) as t
from salaries
group by emp_no
having t > 15
--SQL207：找出所有员工当前薪水salary情况
select distinct salary from salaries
order by salary desc
--SQL209：获取所有非manager的员工emp_no
--解1：
select emp_no
from employees
where emp_no not in (
select emp_no from dept_manager
)
--解2：
select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where d.dept_no is null
--SQL210：获取所有员工当前的manager
select d1.emp_no, d2.emp_no as manager
from dept_emp d1
join dept_manager d2
on d1.dept_no = d2.dept_no
where d1.emp_no != d2.emp_no
--SQL211：获取每个部门中当前员工薪水最高的相关信息
select dept_no, emp_no, salary
from (
select dept_no, d.emp_no, salary,
rank()over(partition by dept_no order by salary desc) as r
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
) s2
where s2.r = 1
--SQL214：查找employees表emp_no与last_name的员工信息
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc
--SQL215：统计出当前各个title类型对应的员工当前薪水对应的平均工资
select title, avg(s.salary)
from titles t
join salaries s
on t.emp_no = s.emp_no
group by title
--SQL216：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no, salary
from (
select emp_no, salary,
rank()over(order by salary desc) as r
from salaries
) s
where s.r = 2
--SQL217：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
select s.emp_no, salary, e.last_name, e.first_name
from salaries s
join employees e
on s.emp_no = e.emp_no
where salary = (
select max(salary) from salaries
where salary != (select max(salary) from salaries)
)
--SQL218：查找所有员工的last_name和first_name以及对应的dept_name
select last_name, first_name, dept_name
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
left join departments d2
on d2.dept_no = d.dept_no
--SQL220：查找在职员工自入职以来的薪水涨幅情况
select se1.emp_no, (s2 - s1) as growth
from (
select s.emp_no, salary s1
from employees e
join salaries s
on s.emp_no = e.emp_no
where from_date = hire_date
) se1
join (
select s.emp_no, salary s2
from employees e
join salaries s
on s.emp_no = e.emp_no
where to_date = '9999-01-01'
) se2
on se1.emp_no = se2.emp_no
order by growth
--SQL221：统计各个部门的工资记录数
select dp.dept_no, dp.dept_name, count(salary) as `sum` -- 着重号可有可无，加上方便与关键字区分开
from departments dp
join dept_emp de
on dp.dept_no = de.dept_no
join salaries s
on s.emp_no = de.emp_no
group by dp.dept_no
order by dp.dept_no
--SQL222：对所有员工的薪水按照salary降序进行1-N的排名
select emp_no, salary,
dense_rank()over(order by salary desc) t_rank
from salaries
order by salary desc, emp_no -- 或者：order by t_rank, emp_no
--SQL223：获取所有非manager员工当前的薪水情况
select de.dept_no, s.emp_no, salary
from employees e, dept_emp de, dept_manager dm, salaries s
where e.emp_no = de.emp_no
and de.dept_no = dm.dept_no
and e.emp_no = s.emp_no
and s.emp_no != dm.emp_no
--SQL224：获取员工其当前的薪水比其manager当前薪水还高的相关信息
select de.emp_no as emp_no, dm.emp_no as manager_no,
s1.salary as emp_salary, s2.salary as manager_salary
from dept_emp de
join dept_manager dm
on de.dept_no = dm.dept_no
join salaries s1
on de.emp_no = s1.emp_no
join salaries s2
on dm.emp_no = s2.emp_no
where s1.salary > s2.salary
--SQL225：汇总各个部门当前员工的title类型的分配数目
select dp.dept_no, dp.dept_name, t.title, count(dp.dept_no) as count
from departments dp
join dept_emp de
on dp.dept_no = de.dept_no
join titles t
on t.emp_no = de.emp_no
group by dp.dept_no, dp.dept_name, t.title
order by dept_no, dept_no
--SQL228：使用join查询方式找出没有分类的电影id以及名称
select f.film_id, f.title
from film f
left join film_category fc
on f.film_id = fc.film_id
where fc.category_id is null
--SQL229：使用子查询的方式找出属于Action分类的所有电影对应的title,description
select title, description from film
where film_id in (
select film_id from film_category
where category_id = (
select category_id from category
where name = 'Action'
)
)
--SQL231：将employees表的所有员工的last_name和first_name拼接起来作为Name
--解1：
select concat(last_name, " ", first_name) as Name from employees
--解2：
select concat_ws(" ", last_name, first_name) as Name from employees
--SQL232：创建一个actor表，包含如下列信息
--解1：primary key 采用“表级约束”，可支持多列组合的复合主键
create table `actor` (
actor_id smallint(5) not null comment '主键id',
first_name varchar(45) not null comment '名字',
last_name varchar(45) not null comment '姓氏',
last_update date not null default (CURRENT_DATE()) comment '最后更新时间，默认是系统的当前时间',
primary key (actor_id)
) -- 或者把 CURRENT_DATE表示当前日期，别名有curdate也可以，CURRENT_TIMESTAMP 表示当前时间，别名有 now，localtime，这些都可以用来替换上面语句中的 CURRENT_DATE
--解2：primary key 也可采用“列级约束”，这种情况仅支持单列主键
create table `actor` (
actor_id smallint(5) primary key not null comment '主键id',
first_name varchar(45) not null comment '名字',
last_name varchar(45) not null comment '姓氏',
last_update date not null default (CURRENT_TIMESTAMP()) comment '最后更新时间，默认是系统的当前时间'
)
--SQL233：批量插入数据
insert into actor (actor_id, first_name, last_name, last_update)
values
(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')
--SQL234：批量插入数据，不使用replace操作
-- 插入数据时，遇到已有重复数据则不操作：insert 后面加 ignore
insert ignore into actor (actor_id, first_name, last_name, last_update)
values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');
--SQL235：创建一个actor_name表
-- 创建表时导入别的表的部分表结构
-- 解1：
/* 使用 CREATE TABLE + SELECT 方法
该方法优点是简单方便，缺点是：
1、除 NOT NULL 外，主键、唯一键、外键等约束不会被复制。
2、不保留默认值：需手动添加（如 CURRENT_TIMESTAMP）。
如果只需要表结构，不需要复制表内容，则在最后面加 where 1=0 即可
*/
create table `actor_name` as
select first_name, last_name from actor
-- 解2：
CREATE TABLE if not exists actor_name (
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
insert into actor_name (first_name, last_name)
select first_name, last_name from actor;
--SQL236：对first_name创建唯一索引uniq_idx_firstname
/* 索引类型与适用场景
索引类型 语法关键字 适用场景 示例
普通索引 INDEX 加速 WHERE 条件查询 CREATE INDEX idx_name ON users (name);
唯一索引 UNIQUE 强制字段值唯一（允许 NULL） CREATE UNIQUE INDEX idx_email ON users (email);
主键索引 PRIMARY KEY 表的唯一标识（不允许 NULL） ALTER TABLE users ADD PRIMARY KEY (id);
全文索引 FULLTEXT 文本内容搜索（如文章、评论） CREATE FULLTEXT INDEX idx_content ON articles (content);
空间索引 SPATIAL 地理位置数据（如经纬度） CREATE SPATIAL INDEX idx_location ON places (location);
组合索引 多列 多字段联合查询（遵循最左前缀原则） CREATE INDEX idx_name_age ON users (name, age);
*/
-- 解1：
create unique index uniq_idx_firstname on actor (first_name);
create index idx_lastname on actor (last_name);
-- 解2：
alter table actor
add unique index uniq_idx_firstname (first_name);
alter table actor
add index idx_lastname (last_name);
--SQL237：针对actor表创建视图actor_name_view
--解1：
create view actor_name_view (first_name_v, last_name_v) as
select first_name, last_name from actor
--解2：
create view actor_name_view as
select
first_name as first_name_v,
last_name as last_name_v
from actor
--SQL238：针对上面的salaries表emp_no字段创建索引idx_emp_no
/* 强制索引是一种手动干预查询优化器的机制，允许开发者显式指定查询应使用的索引，而非依赖优化器的自动选择。
MySQL 查询优化器通常能自动选择最优索引，但在以下场景中可能选择错误：
1、统计信息不准确：表数据分布发生变化，但优化器未及时更新统计信息。
2、索引选择性低：某些索引的区分度不高，优化器误判其有效性。
3、复杂查询：多表连接或子查询中，优化器可能选择次优索引。
4、特殊业务需求：特定场景下，手动指定索引性能更佳。
语法：在 SELECT、UPDATE、DELETE 语句中使用 USE INDEX、FORCE INDEX 或 IGNORE INDEX
1、USE INDEX：指定可使用的索引
2、FORCE INDEX：强制使用指定索引，否则报错
3、IGNORE INDEX: 禁用特定索引
*/
select * from salaries
force index (idx_emp_no)
where emp_no = 10005;
--SQL239：在last_update后面新增加一列名字为create_date
alter table `actor`
add column create_date datetime NOT NULL default '2020-10-01 00:00:00' after last_update
--SQL240：构造一个触发器audit_log
create trigger audit_log
after insert on employees_test -- after 或者 before 都可以，因为根据提议，无所谓先后
for each row
begin
insert into audit (EMP_no, NAME)
values (new.ID, new.NAME); -- 这边的分号不要忘记
end
/*
触发器（Trigger）是一种与表关联的数据库对象，当表上发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行。
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
-- 触发器执行逻辑
END;
参数 说明
trigger_name 触发器名称（需唯一）。
BEFORE/AFTER 指定触发器执行时机：
- BEFORE：在事件执行前触发
- AFTER：在事件执行后触发
INSERT/UPDATE/DELETE 触发事件类型。
FOR EACH ROW 对每一行数据触发一次（MySQL 仅支持行级触发器）。
BEGIN...END 触发器主体逻辑，可包含多条 SQL 语句。
在触发器中，使用 OLD 和 NEW 2个关键字访问触发事件前后的数据：
关键字 适用事件 说明
OLD UPDATE/DELETE 代表修改前或删除前的行数据（只读）。例如：OLD.column_name。
NEW INSERT/UPDATE 代表插入或修改后的行数据。例如：NEW.column_name（INSERT 时为新值，UPDATE 时可修改）。
*/
--SQL241：删除emp_no重复的记录，只保留最小的id对应的记录。
-- 解1：
delete t
from titles_test t
join (select min(id) as min_id, emp_no from titles_test group by emp_no) t2
on t.emp_no = t2.emp_no and t.id != t2.min_id -- 或者 on t.emp_no = t2.emp_no and t.id > t2.min_id
-- 解2：
delete from titles_test
where id not in (
select * from ( select min(id) from titles_test group by emp_no ) a
)
--SQL242：将所有to_date为9999-01-01的全部更新为NULL
update titles_test
set to_date = NULL,
from_date = '2001-01-01'
where to_date = '9999-01-01'
--SQL243：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
-- 解1：
update titles_test
set emp_no = replace(emp_no, 10001, 10005)
where id = 5
-- 解2：
REPLACE INTO titles_test
VALUES(5, 10005 ,'Senior Engineer', '1986-06-26', '9999-01-01') ;
--SQL244：将titles_test表名修改为titles_2017
-- 三种写法：
alter table titles_test rename to titles_2017
alter table titles_test rename as titles_2017
rename table titles_test to titles_2017
--SQL245：在audit表上创建外键约束，其emp_no对应employees_test表的主键id
alter table audit
add CONSTRAINT fk_audit_emp_no
foreign key (EMP_no)
references employees_test(ID)
on delete cascade -- 可不加，加了更严谨
on update cascade -- 可不加，加了更严谨
--SQL247：将所有获取奖金的员工当前的薪水增加10%
-- 解1：
update salaries
set salary = salary * 1.1
where emp_no in (
select emp_no from emp_bonus
)
and to_date = '9999-01-01'
-- 解2：
update salaries s
join emp_bonus e
on s.emp_no = e.emp_no
and to_date = '9999-01-01'
set salary = salary * 1.1
-- 或者：
update salaries s
join emp_bonus e
on s.emp_no = e.emp_no
set salary = salary * 1.1
where to_date = '9999-01-01'
--SQL249：将employees表中的所有员工的last_name和first_name通过引号连接起来。
select concat(last_name,"'",first_name) as name
from employees;
-- 或者：
select concat_ws("'", last_name, first_name) as name
from employees
--SQL250：查找字符串中逗号出现的次数
select id,
length(string) - length(replace(string, ',', '')) as cnt
from strings
--SQL251：获取employees中的first_name
-- 解1：
select first_name from employees
order by right(first_name, 2)
-- 解2：
select first_name from employees
order by substr(first_name, -2, 2) -- substr 和 substring 都可以
--SQL252：按照dept_no进行汇总
select dept_no,
group_concat(emp_no separator ',') as employees
from dept_emp
group by dept_no
--SQL253：平均工资
select avg(salary) as avg_salary
from salaries
where to_date = '9999-01-01'
and salary not in (
select min(salary) from salaries where to_date = '9999-01-01'
)
and salary not in (
select max(salary) from salaries where to_date = '9999-01-01'
)
--SQL254：分页查询employees表，每5行一页，返回第2页的数据
select * from employees
limit 5, 5 -- 两个5的位置和 offset 用法中的位置相反
-- 或者：
select * from employees
limit 5 offset 5
--SQL256：使用含有关键字exists查找未分配具体部门的员工的所有信息。
select * from employees e
where not exists (
select emp_no from dept_emp de
where de.emp_no = e.emp_no
)
--SQL258：获取有奖金的员工相关信息。
select e.emp_no, first_name, last_name, btype, salary,
round(0.1 * btype * salary, 1) as bonus
from employees e
join emp_bonus eb
on eb.emp_no = e.emp_no
join salaries s
on s.emp_no = eb.emp_no
and eb.recevied between from_date and to_date
order by e.emp_no
--SQL259：统计salary的累计和running_total
select emp_no, salary,
sum(salary)over(order by emp_no) as running_total
from salaries
where to_date = "9999-01-01"
--SQL260：给出employees表中排名为奇数行的first_name
-- 解1：
select e.first_name
from employees e
join (
select first_name,
rank() over(order by first_name) rk
from employees
) se
on e.first_name = se.first_name
where se.rk % 2 = 1
-- 解2：
select first_name from (
select emp_no, first_name,
rank() over(order by first_name) rk
from employees
) se
where se.rk % 2 = 1
order by se.emp_no
--SQL261：出现三次以上相同积分的情况
select number from grade
group by number
having count(number) >= 3
--SQL262：刷题通过的题目排名
select id, number,
dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank, id
--SQL263：找到每个人的任务
select p.id, name, content
from person p
left join task t
on p.id = t.person_id
order by p.id
--SQL264：异常的邮件概率
-- 这题直接用 avg + if判断 最简洁
select date, round(avg(if(type = 'no_completed', 1, 0)), 3)
from email e
join user u1 on e.send_id = u1.id and u1.is_blacklist = 0
join user u2 on e.receive_id = u2.id and u2.is_blacklist = 0
group by date
order by date
--SQL265：牛客每个人最近的登录日期(一)
select user_id, date
from (
select user_id, date,
rank()over(partition by user_id order by date desc) rk
from login
) se
where se.rk = 1
order by user_id
--SQL266：牛客每个人最近的登录日期(二)
select u_n, c_n, date
from (
select u.name as u_n, c.name as c_n, date,
rank()over(partition by user_id order by date desc) rk
from login l
join user u on u.id = user_id
join client c on c.id = client_id
) se
where se.rk = 1
order by u_n
--SQL267：牛客每个人最近的登录日期(三)
-- 解1：
select round(count(*) / (select count(distinct user_id) from login), 3)
from (
select user_id, min(date) as date from login group by user_id
) l1
join login l2
on l1.user_id = l2.user_id
and date_add(l1.date, interval 1 day) = l2.date
-- 解2：
select round(count(*) / (select count(distinct user_id) from login), 3)
from login
where (user_id, date) in (
select user_id, date_add(min(date), interval 1 day) as date
from login group by user_id
)
--SQL268：牛客每个人最近的登录日期(四)
-- 解1：
select s.date, sum(if(s.rk = 1, 1, 0)) as new from (
select date, user_id,
rank()over(partition by user_id order by date) rk
from login
) s
group by s.date
-- 解2：
select l.date, count(user_id) as new from (
select user_id, min(date) as date from login group by user_id
) s
right join (select distinct date from login) l
on l.date = s.date
group by l.date
order by l.date
--SQL269：牛客每个人最近的登录日期(五)
-- 这题比较难，解题思路是：先找到每个用户第2次登录的日期，然后去判断在这个日期前也就是第1次登录的日期是否为这第2次登录日期的前一天，sum(第1次登录的日期是第2次登录日期前一天的用户数)/sum(所有第1次登录的用户数)。
-- 注意：要用 dense_rank 确保第2次登录的日期序号为 2；采用 left join 不忽略任何一个日期。
select l.date,
if(sum(if(l.date < l2.date, 1, 0)) = 0,
0.000,
round(sum(if(date_add(l.date, interval 1 day) = l2.date, 1, 0)) / sum(if(l.date < l2.date, 1, 0)), 3)
) p
from login l
left join (
select date, user_id,
dense_rank()over(partition by user_id order by date) rk
from login
) l2
on l.user_id = l2.user_id
and l2.rk = 2
group by l.date
--SQL270：牛客每个人最近的登录日期(六)
select u.name u_n, p.date,
sum(p.number) over(partition by p.user_id order by p.date) as ps_num
from passing_number p
join user u on u.id = p.user_id
order by p.date, u_n
--SQL271：考试分数(一)
select job, round(avg(score), 3) as avg
from grade
group by job
order by avg(score) desc
--SQL272：考试分数(二)
select id, g.job, score
from grade g
join (
select job, avg(score) avg from grade
group by job
) s
on g.job = s.job and score > avg
order by id
--SQL273：考试分数(三)
select g.id, name, score
from (
select id, language_id, score,
dense_rank() over(partition by language_id order by score desc) as rk
from grade
) g
join language l on g.language_id = l.id
and rk <= 2
order by name, score desc, g.id
--SQL274：考试分数(四)
-- 这个方法很妙，不用再判断单复数了
select job,
floor((count(score) + 1) / 2) as start,
floor((count(score) + 2) / 2) as end
from grade
group by job
order by job
--SQL275：考试分数(五)
select id, s1.job, score, t_rank
from (
select id, job, score, count(*) over(partition by job) as total,
row_number() over(partition by job order by score desc) as t_rank
from grade
) s1
where t_rank in (floor((total + 1) / 2), floor((total + 2) / 2))
order by id
--SQL276：牛客的课程订单分析(一)
select * from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
order by id
--SQL277：牛客的课程订单分析(二)
select user_id from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(id) >= 2
order by user_id
--SQL278：牛客的课程订单分析(三)
-- 解1：妙用 count() over() 窗口函数
select id, user_id, product_name, status, client_id, date
from (
select id, user_id, product_name, status, client_id, date,
count(*) over(partition by user_id) as cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
) s
where cnt >= 2
order by id
-- 解2：
select * from order_info
where user_id in (
select user_id from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(id) >= 2
)
and date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
--SQL279：牛客的课程订单分析(四)
select user_id, min(date) as first_buy_date, cnt
from (
select user_id, date,
count(*) over(partition by user_id) as cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
) s
where cnt >= 2
group by user_id
order by user_id
--SQL280：牛客的课程订单分析(五)
select user_id, min(date) as first_buy_date,
max(date) as second_buy_date, cnt
from (
select user_id, date,
row_number() over(partition by user_id order by date) rk,
count(*) over(partition by user_id) as cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
) s
where cnt >= 2 and rk <= 2
group by user_id
order by user_id
--SQL281：牛客的课程订单分析(六)
-- NULL 值无需额外设置，只要一个 left join 就可以解决
select s.id, is_group_buy, c.name as client_name
from (
select id, client_id, is_group_buy,
count(*) over(partition by user_id) as cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
) s
left join client c on c.id = s.client_id
where cnt >= 2
order by s.id
--SQL282：牛客的课程订单分析(七)
select
ifnull(c.name, 'GroupBuy') as source, --或者：if(c.name <=> null, 'GroupBuy', c.name)
count(s.id) as cnt
from (
select id, client_id,
count(*) over(partition by user_id) as cnt2
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
) s
left join client c on c.id = s.client_id
where cnt2 >= 2
group by c.name
order by source
--SQL283：实习广场投递简历分析(一)
select job, sum(num) as cnt from resume_info
where year(date) = 2025
group by job
order by cnt desc
--SQL284：实习广场投递简历分析(二)
-- 解1：
-- 注意：select 里面的字段和 group by 后面的对应字段必须相同
select job,
date_format(date, "%Y-%m") as mon,
sum(num) as cnt
from resume_info
where year(date) = 2025
group by job, date_format(date, "%Y-%m")
order by mon desc, cnt desc
-- 解2：
-- lpad() 方法可以补齐月份前面缺少的 ‘0’
select job,
concat('2025-', lpad(month(date), 2, 0)) as mon,
sum(num) as cnt
from resume_info
where year(date) = 2025
group by job, concat('2025-', lpad(month(date), 2, 0))
order by mon desc, cnt desc
--SQL285：实习广场投递简历分析(三)
select s1.job, first_year_mon, first_year_cnt,
second_year_mon, second_year_cnt
from (
select job,
date_format(date, "%Y-%m") as first_year_mon,
sum(num) as first_year_cnt
from resume_info
where year(date) = 2025
group by job, date_format(date, "%Y-%m")
) s1
join (
select job,
date_format(date, "%Y-%m") as second_year_mon,
sum(num) as second_year_cnt
from resume_info
where year(date) = 2026
group by job, date_format(date, "%Y-%m")
) s2 on s1.job = s2.job
and right(first_year_mon, 2) = right(second_year_mon, 2)
order by first_year_mon desc, s1.job desc
--SQL286：最差是第几名(一)
select grade,
sum(number) over(order by grade) as t_rank
from class_grade
order by grade
--SQL287：最差是第几名(二)
-- 解1：最优解，中位数的最佳算法！
select grade
from (
select grade,
sum(number) over(order by grade desc) as a,
sum(number) over(order by grade) as b,
(select sum(number) from class_grade) as total
from class_grade
) s where a >= total / 2 and b >= total / 2
-- 解2：
select grade from (
select grade,
ifnull(1 + lag(end) over(order by grade), 1) as start,
end, total
from (
select grade,
sum(number) over(order by grade) as end,
(select sum(number) from class_grade) as total
from class_grade
) s
) s2 where floor((total + 1) / 2) between start and end
or floor((total + 2) / 2) between start and end
-- 解3：
select grade from (
select grade,
ifnull(1 + lag(end) over(order by grade), 1) as start,
end
from (
select grade, sum(number) over(order by grade) as end from class_grade
) s
) s2 where floor(((select sum(number) from class_grade) + 1) / 2) between start and end
or floor(((select sum(number) from class_grade) + 2) / 2) between start and end
--SQL288：获得积分最多的人(一)
select name, sum(grade_num) over(partition by user_id) as grade_num
from grade_info g
join user u on id = user_id
order by grade_num desc
limit 1
--SQL289：获得积分最多的人(二)
select id, name, grade_num
from (
select user_id, sum(grade_num) as grade_num,
dense_rank() over(order by sum(grade_num) desc) as rk
from grade_info group by user_id
) s
join user u on u.id = s.user_id
and rk = 1
--SQL290：获得积分最多的人(三)
select id, name, grade_num
from (
select user_id,
sum(if(type = 'reduce', grade_num * (-1), grade_num)) as grade_num,
dense_rank() over(order by sum(if(type = 'reduce', grade_num * (-1), grade_num)) desc) as rk
from grade_info
group by user_id
) s
join user u on u.id = s.user_id and rk = 1
--SQL291：商品交易(网易校招笔试真题)
select g.id, name, weight, sum(count) as total
from goods g
join trans t
on g.id = t.goods_id
and weight < 50
group by 1, 2, 3
having sum(count) > 20
order by 1
--SQL292：网易云音乐推荐(网易校招笔试真题)
select music_name from music where id in (
select distinct music_id from music_likes
where user_id in (select follower_id from follow where user_id = 1)
and music_id not in (select music_id from music_likes where user_id = 1)
)
order by id
--SQL293：今天的刷题量(一)
-- 官方日期出错，必须在日期上减一天答案才能通过
-- 之后也可能会恢复正常，如恢复正常，则删掉 date_sub(, interval 1 day) 即可
select name, count(create_time) as cnt
from submission sm
join subject sj
on subject_id = sj.id
and create_time = date_sub(curdate(), interval 1 day) -- 把 curdate() 改为 date(now()) 也是可以的
group by subject_id, 1
order by 2 desc, subject_id四、SQL大厂笔试真题（2025-06-13更新版）
--SQL40：23年蚂蚁-每个月Top3的周杰伦歌曲
select month, ranking, song_name, play_pv from (
select month(fdate) as month,
rank() over(partition by month(fdate) order by count(*) desc, s.song_id) as ranking,
song_name, count(*) as play_pv
from play_log p
join song_info s on s.song_id = p.song_id
join user_info u on u.user_id = p.user_id
where year(fdate) = 2022 and s.singer_name = '周杰伦' and age between 18 and 25
group by 1, 3, s.song_id
) s
where ranking <= 3
order by 1, 2
--SQL41：23年蚂蚁-最长连续登录天数
-- 解1：这里妙用了一个 date_sub + row_number 来制定一个连续日期的标签：
select user_id, max(consec_days) as max_consec_days
from (
select user_id, count(*) as consec_days from (
select user_id, fdate,
date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as flag
from tb_dau where fdate between '2023-01-01' and '2023-01-31'
) s
group by user_id, flag
) s2
group by user_id
-- 将 解1 改写成 with 形式
with s as (
select user_id, fdate,
date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as flag
from tb_dau where fdate between '2023-01-01' and '2023-01-31'
),
s2 as (
select user_id, count(*) as consec_days from s
group by user_id, flag
)
select user_id, max(consec_days) as max_consec_days
from s2
group by user_id
--SQL42：23年蚂蚁-分析客户逾期情况
select pay_ability,
concat(round(sum(if(overdue_days <=> null, 0, 1))/count(agreement_id) * 100,1) , '%') as overdue_ratio
from customer_tb c
join loan_tb l on l.customer_id = c.customer_id
group by 1
order by 2 desc
--SQL43：23年蚂蚁-获取指定客户每月的消费额
select date_format(t_time, '%Y-%m') as time,
round(sum(t_amount), 1) as total
from trade t
join customer c on c_id = t_cus
where c_name = 'Tom' and year(t_time) = 2023 and t_type = 1
group by 1
order by 1
--SQL44：23年蚂蚁-查询连续入住多晚的客户信息？
select user_id, c.room_id, room_type,
datediff(checkout_time, checkin_time) as days
from checkin_tb c
join guestroom_tb g on c.room_id = g.room_id
where date(checkin_time) = '2022-06-12'
and datediff(checkout_time, checkin_time) >= 2
order by 4, 2, 1 desc
--SQL45：23年蚂蚁-统计所有课程参加培训人次
select round(sum(length(course) - length(replace(course, ',', '')) + 1)) as staff_nums
from cultivate_tb
--SQL46：23年蚂蚁-查询培训指定课程的员工信息
select s.staff_id, staff_name
from staff_tb s
join cultivate_tb c on c.staff_id = s.staff_id
where course like '%course3%' -- 或者：where find_in_set('course3', course) > 0
order by 1
--SQL47：23年蚂蚁-推荐内容准确的用户平均评分
select round(sum(score)/count(distinct user_id), 3) as avg_score from (
select distinct user_id, score
from user_action_tb u
join recommend_tb r on rec_user = user_id and rec_info_l = hobby_l
) s
--SQL48：23年携程-每个商品的销售总额
select name as product_name, sum(quantity) as total_sales,
rank() over(partition by category order by sum(quantity) desc) as category_rank
from orders o
join products p on p.product_id = o.product_id
group by o.product_id
order by category, 2 desc
--SQL49：22年携程-统计各岗位员工平均工作时长
select post,
round(sum(timestampdiff(second, first_clockin, last_clockin)) / 3600 / count(*), 3) as work_hours
from staff_tb
join attendent_tb using(staff_id)
where first_clockin is not null and last_clockin is not null
group by 1
order by 2 desc
-- 或者：
select post,
round(sum(timestampdiff(second, first_clockin, last_clockin)) / 3600 / count(*), 3) as work_hours
from staff_tb s
join attendent_tb a on a.staff_id = s.staff_id
where first_clockin is not null and last_clockin is not null
group by 1
order by 2 desc
--SQL50：22年携程-查询连续登陆的用户
select user_id from (
select user_id, date_sub(date, interval row_number()over(partition by user_id order by date) day) as flag from (
select distinct user_id, date(log_time) as date from login_tb
) s
) s2
join register_tb using(user_id)
group by 1, flag
having count(flag) >= 3
order by 1
--SQL51：23年携程-统计商家不同会员每日访问人次及访问人数
select vip, count(info_id) as visit_nums, count(distinct v.user_id) as visit_users
-- 或者：coalesce(sum(order_price), 0) as order_total
from visit_tb v
join uservip_tb using(user_id)
group by vip
order by visit_nums desc
--SQL52：23年携程-统计各等级会员用户下订单总额
select vip, ifnull(sum(order_price), 0) as order_total
from order_tb
right join uservip_tb using(user_id)
group by vip
order by 2 desc
--SQL53：23年携程-查询下订单用户访问次数？
select user_id, count(distinct info_id) as visit_nums
from order_tb
join visit_tb using(user_id)
where date_format(order_time, '%Y-%m-%d') = '2022-09-02'
and date_format(visit_time, '%Y-%m-%d') = '2022-09-02'
group by 1
order by 2 desc
--SQL54：23年携程-统计用户从访问到下单的转化率
--SQL55：23年携程-统计员工薪资扣除比例
--SQL56：24年交银金科-统计用户获得积分
--SQL57：22年携程-更新用户积分信息？
--SQL58：22年携程-查询单日多次下订单的用户信息？
--SQL59：22年携程-统计各个部门平均薪资
--SQL60：22年携程-统计加班员工占比
--SQL61：22年携程-每天登陆最早的用户的内容喜好
--SQL62：22年携程-支付间隔平均值
--SQL63：22年网易-网易云音乐推荐
--SQL64：22年网易-商品交易
--SQL65：23年知乎-请写出计算粉丝ctr的sql语句
--SQL66：23年掌阅-查询成绩
--SQL67：24年OPPO-被重复观看次数最多的3个视频
--SQL68：24年OPPO-短视频直播间晚上11-12点之间各直播间的在线人数
--SQL69：23年阿里-淘宝店铺的实际销售额与客单价
--SQL70：23年阿里-完成员工考核试卷突出的非领导员工
--SQL71：23年京东-查询产生理赔费用的快递信息
--SQL72：23年京东-统计快递运输时长
--SQL73：23年京东-统计快递从创建订单到发出间隔时长
--SQL74：23年京东-下单最多的商品
--SQL75：23年京东-用户购买次数前三
--SQL76：23年京东-商品价格排名
--SQL77：23年京东-商品销售排名
--SQL78：23年京东-商品销售总额分布
--SQL79：24年京东-每个客户的账户总金额
--SQL80：24年京东-每个部门薪资排名前两名员工
--SQL82：24年京东-查询订单
--SQL83：24年京东-商品id数据清洗统计
--SQL84：24年京东-每个顾客最近一次下单的订单信息
--SQL85：24年阿里-统计每个产品的销售情况
--SQL86：24年京东-各个部门实际平均薪资和男女员工实际平均薪资
--SQL87：24年京东-每个顾客购买的最新产品名称
--SQL88：24年京东-输出播放量最高的视频
--SQL89：24年京东-返回顾客名称和相关订单号以及每个订单的总价
--SQL90：24年京东-未下单用户统计
--SQL92：24年京东-用户订单信息查询
--SQL93：24年京东-未下单用户登陆渠道统计
--SQL94：24年京东-更新员工信息表
--SQL95：24年京东-最受欢迎的top3课程
--SQL96：24年京东-对商品的销售情况进行深度分析
--SQL97：24年京东-电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评估
--SQL98：24年京东-电商平台想要了解不同商品在不同月份的销售趋势
--SQL99：24年京东-分析每个商品在不同时间段的销售情况
--SQL100：24年京东-查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息
--SQL101：24年京东-分析每个员工在不同项目中的绩效情况
--SQL102：24年京东-查询出每个品牌在特定时间段内的退货率以及平均客户满意度评分
--SQL103：24年京东-物流公司想要分析快递小哥的薪资构成和绩效情况
--SQL104：24年京东-查询出每个品牌在不同月份的总销售额以及购买该品牌商品的用户的平均年龄
--SQL105：24年京东-电商平台需要对各行业销售情况综合评估
--SQL106：24年京东-电商平台想要查询出每个商品在 2024 年上半年（1 月至 6 月）的总销售额
--SQL107：24年京东-电商平台需要对商品的销售和评价情况进行综合分析
--SQL108：24年京东-评估2023年不同品牌商品的销售趋势和客户满意度
--SQL109：24年京东-查询出每个运输方式在不同城市的平均运输时长以及总运输费用
--SQL110：24年京东-分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况
--SQL111：24年京东-物流公司想要分析快递小哥的收入情况
--SQL112：24年京东-分析不同门店各类商品的库存情况和销售情况
--SQL113：24年京东-评估不同供应商提供的零部件质量和成本情况
--SQL114：24年京东-了解 2023 年全年所有商品的盈利情况
--SQL115：24年京东-哪些产品在特定时间段内表现最为出色