SQL206：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列。
SELECT
a.dept_no,
b.emp_no,
b.salary
FROM
dept_emp a
JOIN salaries b ON a.emp_no = b.emp_no
JOIN (
SELECT # 每个部门的最大薪水表
a.dept_no,
max( b.salary ) max_salary
FROM
dept_emp a
JOIN salaries b ON a.emp_no = b.emp_no
GROUP BY
a.dept_no
) c ON a.dept_no = c.dept_no
and a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
WHERE
b.salary = c.max_salary # 关键条件
ORDER BY
a.dept_no ASC
SQL212：请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成。
# 方法一 自联结
select *
from employees as emp
join salaries as sal
on emp.emp_no = sal.emp_no
where salary = (
select s1.salary
from salaries s1 join salaries s2 on s1.salary <= s2.salary
group by s1.salary
having count(distinct s2.salary) = 2 # 这里 =2 是因为第二大的薪水按照 on 后面的过滤条件来的，因为第二大的薪水只小于等于第一大薪水和自己，所以连接表之后有两条数据，如果题目要求是第三大薪水，那么这里就应该 =3
)
# 方法二 刨除原表的最大薪水的最大薪水就是第二大薪水
select *
from employees as emp
join salaries as sal
on emp.emp_no = sal.emp_no
where salary = (
select max(salary)
from salaries
where salary <
(select max(salary)
from salaries)
)
SQL215:请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
# 错误答案1： 没考虑员工的薪水是多次增长的，不是一蹴而就就涨到现在水平的
# select emp_no,(lead1 - salary) as growth
# from
# (select a.emp_no,salary,lead(salary) over(partition by a.emp_no order by salary ) as lead1
# from
# (select emp.emp_no # 仍在职员工
# from employees as emp
# left join salaries as sal
# on emp.emp_no = sal.emp_no
# where to_date =
# join salaries sal
# on a.emp_no = sal.emp_no) b
# where lead1 is not null
# order by growth
# 错误答案2： 不应该用最大薪水 - 最小薪水 因为还有降薪问题，应该用当前薪水 - 入职薪水
# select emp_no,(maxSalary - minSalary) as growth
# from
# (select emp_no,
# sum(case when rank_min = 1 then salary else 0 end) as minSalary,
# sum(case when rank_max = 1 then salary else 0 end) as maxSalary
# from
# (select a.emp_no,salary,row_number() over(partition by a.emp_no order by salary ) as rank_min,row_number() over(partition by a.emp_no order by salary desc) as rank_max
# from
# (select emp.emp_no # 仍在职员工
# from employees as emp
# left join salaries as sal
# on emp.emp_no = sal.emp_no
# where to_date =
# join salaries sal
# on a.emp_no = sal.emp_no) b
# group by emp_no) c
# order by growth
# 正确答案： 当前薪水 - 入职薪水
select a.emp_no,b.salary - a.salary as growth
from
(select emp.emp_no,salary
from employees as emp
left join salaries sal
on emp.emp_no = sal.emp_no
where hire_date = from_date) a
join
(select emp.emp_no,salary
from employees as emp
left join salaries sal
on emp.emp_no = sal.emp_no
where to_date =
on a.emp_no = b.emp_no
order by growth
SQL217:对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列
select emp_no,salary,
-- 根据题意可知，有可能会出现同名次的情况，所以排除row_number() 函数
-- 至于而rank()会出现跳名次情况，也不太符合情况，所以选择dense_rank()
dense_rank() over(order by salary desc) as t_rank
from salaries
WHERE to_date = '9999-01-01'
SQL218：获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary
select dept_no,emp.emp_no,salary
from employees emp
left join dept_emp dept
on emp.emp_no= dept.emp_no
left join salaries sal
on emp.emp_no = sal.emp_no
where emp.emp_no not in (select emp_no from dept_manager)
SQL:219：获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary
select dept.emp_no as eeno ,man.emp_no as manno,sal.salary as eesal,sal2.salary as mansal
from dept_emp as dept
left join dept_manager as man
on dept.dept_no = man.dept_no
left join salaries sal
on dept.emp_no = sal.emp_no
left join salaries sal2
on man.emp_no = sal2.emp_no
where dept.to_date = '9999-01-01'
and dept.emp_no != man.emp_no
and sal2.salary < sal.salary -- 关键条件
SQL220：汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序
select d.dept_no,
d.dept_name,
t.title,
count(t.title)as count
from departments d,dept_emp de,titles t
where de.emp_no=t.emp_no
and de.dept_no=d.dept_no
and de.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by d.dept_no,t.title
SQL254：按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推
-- 注意sum() over()的用法
select emp_no,salary,sum(salary) over(order by emp_no) as run_total
from salaries
where to_date = '9999-01-01'
SQL255：请你在不打乱原序列顺序的情况下，输出：按first_name排升序后，取奇数行的first_name
select first_name
from
(select *,row_number() over(order by first_name) as first_rank
from employees) a
where mod(first_rank,2) != 0
order by emp_no
select first_name
from employees
where first_name in
(select first_name
from
(select *,row_number() over(order by first_name) as first_rank
from employees) a
where mod(first_rank,2) != 0)
SQL259： 统计正常用户发送给正常用户邮件失败的概率:有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。
select date,round(sum(if(type = 'no_completed',1,0)) / count(*),3) as p
from email
where send_id not in
(select id
from user
where is_blacklist = 1)
and
receive_id not in
(select id
from user
where is_blacklist = 1)
group by date
SQL261：请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.有一个登录(login)记录表
select u_n,c_n,date
from
(select u.name as u_n,c.name as c_n,date,row_number() over(partition by l.user_id order by date desc) date_rank
from login l
left join user u
on l.user_id = u.id
left join client c
on l.client_id = c.id)a
where date_rank = 1
order by u_n
SQL262：请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)
SELECT
ROUND(COUNT(DISTINCT user_id)*1.0 / (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) FROM login GROUP BY user_id);
【*】SQL263：请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下
select date ,
count(distinct case when (user_id,date) in
(select user_id,min(date)from login group by user_id)
then user_id else null end)
from login
group by date
order by date
select a.date, count(b.user_id) new
from (select distinct date from login) a
left join (select user_id, min(date) first_date from login group by user_id) b on a.date=b.first_date
group by a.date
order by a.date
【*】SQL264：请你统计一下牛客每个日期新用户的次日留存率
select t0.date,
ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)
from
(
select date
from login
group by date
) t0
left join
(
select user_id,min(date) as date
from login
group by user_id
)t1
on t0.date=t1.date
left join login as t2
on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
group by t0.date
SQL265：请你统计一下牛客每个用户刷题情况，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但存在登录了没刷题的情况，不会存在刷题表里面，会存在提交代码没有通过的情况并记录在刷题表里，通过数目是0
因为有登录没有刷题的数据不需要输出，所以截止到某天指的是刷题表有记录的日期。没有用到登录表
SELECT u.name, p.date, SUM(p.number)over(PARTITION BY u.id ORDER BY p.date)
FROM passing_number AS p, user AS u
WHERE p.user_id = u.id
ORDER BY p.date, u.name;
SQL268：请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
select id,name,score
from
(select ga.id,name,score,dense_rank() over(partition by name order by score desc) as score_rank
from grade ga
left join language as la
on ga.language_id = la.id) a
where score_rank <= 2
order by name,score desc,id
select id,name,score
from
(select ga.id,name,score,row_number() over(partition by name order by score desc) as score_rank
from grade ga
left join language as la
on ga.language_id = la.id) a
where score_rank <= 2
【*】SQL269：请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序
-- 重点是这里的数学思想
select job, if(mod(cnt,2) != 0,FLOOR(cnt/2) + 1,floor(cnt/2)) as str,
if(mod(cnt,2) != 0,floor(cnt/2)+1,floor(cnt/2) + 1) as ed
from(
select job ,count(score) as cnt
from grade
group by job) a
order by job
【*】SQL270：请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序
with t_rank as
(
select *,
count(score) over(partition by job) as total,
row_number() over(partition by job order by score,id) as a, #升序序号
row_number() over(partition by job order by score desc,id desc) as b #逆序序号
from grade
)
select id,job,score,b
from t_rank
where a>=total/2 and b>=total/2
order by id
SQL274：请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及所有日期里购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序
方法1：
select user_id,date,p_c
from
(select a.user_id,p_c,date,row_number() over(partition by user_id order by date ) as date_rank
from
(select user_id,count(product_name) as p_c
from order_info
where date(date) > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')
group by user_id ) a
left join order_info
on a.user_id = order_info.user_id
and date(date) > '2025-10-15'
where p_c >=2) b
where date_rank = 1
方法2：更精简
select user_id,min(date),count(user_id) as cnt
from order_info
where date(date) > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')
group by user_id
having count(user_id) >= 2
SQL275：请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序
with re as
(select *,count(product_name) over(partition by user_id) as cnt ,row_number() over(partition by user_id order by date) as date_rank
from order_info
where date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')
)
select user_id,
max(if(date_rank = 1,date,0)) as fst_date,
max(if(date_rank = 2,date,0)) as sec_date,
cnt
from re
where cnt > 1
group by user_id,cnt
order by user_id
SQL277：请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序
select (case when is_group_buy = 'No'
then c.name
else 'GroupBuy'
end) as source,
count(t.id) as cnt
from (select *,
count(id)over(partition by user_id) as num
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')) as t
left join client c on t.client_id = c.id
where t.num >= 2
group by source
order by source
SQL280：请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示
select a.job,first_mon,fir_sum,sec_mon,sec_sum
from
(select job,DATE_FORMAT(date,'%Y-%m') as first_mon,sum(num) as fir_sum
from resume_info
where year(date) = '2025'
group by job,first_mon) a
inner join
(select job,DATE_FORMAT(date,'%Y-%m') as sec_mon ,sum(num) as sec_sum
from resume_info
where year(date) = '2026'
group by job,sec_mon) b
on a.job = b.job
and right(first_mon,2) = right(sec_mon,2)
order by first_mon desc,a.job desc
SQL282：老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出
-- 还是中位数问题
with mid as (
select * ,(select sum(number) from class_grade) as total,
sum(number) over(order by grade) as ac,
sum(number) over(order by grade desc) as dc
from class_grade
order by grade )
select grade
from mid
where ac >= total/2 and dc >= total/2 # 这里是都要大于等于 取交集
order by grade
SQL284：请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序
select user_id,name,mx
from
(select user_id,max(total_sum) as mx,rank() over(order by max(total_sum) desc) as g_rank
from
(select * ,sum(grade_num) over(partition by user_id order by grade_num) as total_sum
from grade_info) a
group by user_id) b
left join user as u
on b.user_id = u.id
where g_rank = 1
order by user_id
SQL285：请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序
方法1：
select user_id,name,mx
from
(select user_id,name,mx,rank() over( order by mx desc) as g_rank
from
(select user_id,max(total_sum) as mx
from
(select *,sum(eg) over(partition by user_id order by user_id) as total_sum
from
(select * ,case when type = 'add' then grade_num else -grade_num end as eg
from grade_info) a) b
group by user_id) c
left join user as u
on c.user_id = u.id) d
where g_rank = 1
order by user_id
方法2：用if代替case when 这一步直接简化了sum窗口函数和casewhen判断负数
select user_id,name,grade
from
(select *,rank()over(order by grade desc) as g_rank
from
(select user_id,sum(if(type = 'add',grade_num,-1*grade_num)) as grade
from grade_info
group by user_id) a) b
left join user as u
on b.user_id = u.id
where g_rank = 1
SQL287：请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
错误版本：
select distinct music_name
from
(select follower_id
from follow
where user_id = 1) a
left join music_likes as mt
on a.follower_id = mt.user_id
left join music
on mt.music_id = music.id
where music_name not in (select music_name from(select user_id from follow where user_id = 1) b left join music_likes as mlk on b.user_id = mlk.user_id left join music on mlk.music_id = music.id )
order by id
这里报错了 - 3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column
-- 因为将distinct 和 order by 一起使用导致的，因为先执行distinct去重产生虚拟表，然后再在虚拟表上进行orderby 由于虚拟表没有order by的字段，所以报错了，也就是说order by 必须在select中出现
-- 解决办法：可以用group by 代替distinct
-- 解决办法：也可以将distinct 放在子查询里面
select music_name
from follow t1 join music_likes t2 on follower_id=t2.user_id
join music t3 on t2.music_id=t3.id
where t1.user_id=1 and t3.music_name not in (
select music_name
from music_likes t2 join music t3 on t2.music_id=t3.id
where t2.user_id=1)
group by t2.music_id
order by t2.music_id
