牛客刷题——SQL实战 笔记

牛客刷题，记录争议较多的题目的自己的解法。

第1题：查找最晚入职员工的所有信息
子查询

select * from employees
where hire_date=(select max(hire_date) from employees);

开窗函数

select a.emp_no, a.birth_date, a.first_name, a.last_name,a.gender,a.hire_date
from (
    select *, dense_rank() over(order by hire_date desc) as rk
    from employees) a
where rk =1;

用户变量

select aaa.emp_no,aaa.birth_date, aaa.first_name,aaa.last_name,aaa.gender,aaa.hire_date
#不用表别名也对
from (
select p.*,
       if(@prev = hire_date,@rk :=@rk,@rk :=@rk+1) as rkk,
       @prev :=hire_date
from employees p,(select @rk :=0, @prev :=null)q 
order by hire_date desc) aaa
where rkk=1;

第2题：查找入职员工时间排名倒数第三的员工所有信息
报错：

select * from employees
where hire_date 
in(        #不支持IN里面的语句使用LIMIT
    select distinct hire_date from employees
    order by hire_date desc
    limit 2,1) ;

错因分析

修改1：

select * from employees
where hire_date 
in(select * from (
    select distinct hire_date from employees
    order by hire_date desc
    limit 2,1) t);

修改2：

select * from employees
where hire_date 
=(#改为等号即对
    select distinct hire_date from employees
    order by hire_date desc
    limit 2,1) ;

第12题 获取每个部门中当前员工薪水最高的相关信息

select b.dept_no,a.emp_no,b.maxsalary
from
(select p.dept_no,p.emp_no,q.salary
from dept_emp p inner join salaries q
on p.emp_no = q.emp_no)a,
(select e.dept_no,max(s.salary) as maxsalary#这里不能select emp_no,group by和max出现时的坑
from dept_emp e inner join salaries s
on e.emp_no =s.emp_no
group by dept_no)b
where a.dept_no=b.dept_no and a.salary=b.maxsalary
order by dept_no;

select dept_no,emp_no,salary as maxsalary from(
select d.dept_no,d.emp_no,s.salary,
dense_rank() over(partition by d.dept_no order by s.salary desc) as rk
from dept_emp d join salaries s
on d.emp_no =s.emp_no ) a
where rk=1;

第16题 统计出各个title类型对应的员工薪水对应的平均工资

select t.title,avg(s.salary)
from titles t inner join salaries s
on t.emp_no = s.emp_no
group by t.title
order by avg(s.salary);
#这个题最后输出的结果只有title和salary，这就不像12题中会出现 group by和聚合函数一起出现时的坑

第17题 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries
where salary =
(select salary from salaries
where salary < (select max(salary) from salaries)
order by salary desc
limit 1);

select emp_no, salary from salaries
where salary =
(select distinct salary from salaries order by salary desc limit 1,1);

第18题 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
与17题的表不同
先用max函数做了下

select s.emp_no,s.salary,e.last_name,e.first_name
from
employees e,
(select emp_no,salary from salaries
 where salary=
(select max(salary) from salaries
where salary < (select max(salary) from salaries)))s
where e.emp_no=s.emp_no;

再用count函数做了一下

select s.emp_no,s.salary,e.last_name,e.first_name
from employees e, salaries s 
where e.emp_no = s.emp_no and s.salary =
(select s1.salary
from salaries s1 inner join salaries s2
on s1.salary <s2.salary
group by s1.salary
having count(distinct s2.salary)=1);

第22题 统计各个部门的工资记录数

select distinct a.dept_no,a.dept_name,
count(a.salary) over(partition by a.dept_no) as 'sum'
#用开窗函数会保留原记录数量，不聚合，所以select中加了distinct
#over中没用order by，因为考虑到有它后count作用范围是分区第一行到当前值，怕出错
from(
select s.emp_no,de.dept_no,d.dept_name,s.salary
from salaries s left join dept_emp de on s.emp_no = de.emp_no
                left join departments d on de.dept_no = d.dept_no) a
order by dept_no;

select a.dept_no,a.dept_name,count(a.salary) as 'sum'
from(
select s.emp_no,de.dept_no,d.dept_name,s.salary 
from salaries s left join dept_emp de on s.emp_no = de.emp_no
                left join departments d on de.dept_no = d.dept_no) a
group by a.dept_no,a.dept_name
#一开始不打算用group by是因为考虑到select中有dept_name,不过这个题把它加在这里不影响
order by dept_no;

第24题 对所有员工的薪水按照salary进行按照1-N的排名
dense_rank()开窗函数就不写了，好喜欢用变量啊

select emp_no,salary,t_rank from
(select emp_no, salary,
if(@prev = salary, @dsrank := @dsrank, @dsrank := @dsrank +1) as t_rank,
@prev :=salary
from salaries s,(select @prev := null, @dsrank :=0) r
order by salary desc,emp_no) a;#一开始不对，因为没注意到“相同salary并列且按照emp_no升序排列”

联结+count

select a.emp_no,s.salary,a.t_rank
from salaries s,
(select s1.emp_no,count(distinct s2.salary) as t_rank
from salaries s1, salaries s2
where s1.salary <= s2.salary
group by s1.emp_no) a
where s.emp_no=a.emp_no
order by salary desc,emp_no;

2021年5月22日 真切体会到“十里长街送总理”的情愫。
袁老先生千古，关于水稻的梦一定都会成真。

第26题 汇总各个部门当前员工的title类型的分配数目

select de.dept_no,d.dept_name,t.title,count(1) as 'count'
#count（1）或者（*）是最简便的，具体的列的话我老忘了加表别名==#
from dept_emp de,titles t,departments d
where de.emp_no = t.emp_no and de.dept_no=d.dept_no
group by de.dept_no,d.dept_name,t.title
order by dept_no;

第32题 将employees表的所有员工的last_name和first_name拼接起来作为Name

select concat_ws(' ',last_name,first_name) as name
from employees;
#''不行，' '行,空格不能省略==#

select concat(last_name,' ',first_name) as name
from employees;

第34&35题 插入数据

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');

如果数据已经存在，请忽略 ignore用法

insert ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

第61题 对于employees表中，给出奇数行的first_name
这样写，但是输出的顺序与答案不一致

select a.first_name from
(
select first_name,
(row_number() over(order by first_name asc)) as rk
from employees
) a
where a.rk & 1 = 1;

才看到题目描述最下面那句话–输出时不需排序，所以就是按照原表中的顺序输出。
则原基础上修改为：

select first_name from
employees where first_name in
(
select a.first_name from
(
select first_name,
(row_number() over(order by first_name asc)) as rk
from employees
) a
where a.rk & 1 = 1);

第67题 牛客每个人最近的登录日期(二)
想到这个client name在用group by时候容易出问题
用开窗函数解决

select a.user_name as u_n,a.client_name as c_n,a.date
from
(
select l.id,
    l.user_id,u.name as user_name,
    l.client_id,c.name as client_name,
    l.date,
    rank() over(partition by l.user_id order by l.date desc) as rk
from login l,user u,client c
where l.user_id=u.id and l.client_id=c.id
) a
where rk=1
order by u_n;

不用开窗函数时候，下面这个答案比我写的简洁，学习下

select u.name as u_n, c.name as c_n, l.date as d
from login as l
inner join user as u on l.user_id=u.id
inner join client as c on l.client_id=c.id
where (l.user_id, l.date) in (
    select user_id, max(date) 
    from login 
    group by user_id     #where还能这样用
)
order by u.name asc

第86题 实习广场投递简历分析(三)
第一遍

select a.job,a.year_mon as first_year_mon,a.cnt as first_year_cnt,
       b.year_mon2 as second_year_mon,b.ct as second_year_cnt
from
(
select job,date_format(date,'%Y-%m') as year_mon,
sum(num) as cnt
from resume_info
where year(date) ='2025'
group by job,date_format(date,'%Y-%m')
) a,
(
select job,date_format(date,'%Y-%m') as year_mon2,
sum(num) as ct
from resume_info
where year(date) ='2026'
group by job,date_format(date,'%Y-%m')
) b
where a.job=b.job and month(a.year_mon)=month(b.year_mon2)
order by first_year_mon desc,job desc;

错误原因在于“利用date_format转化后的日期不能再用month函数”，month()函数的参数只能是完整的【年-月-日】

正确：

select a.job, 
    date_format(d1, '%Y-%m') first_year_mon, 
    c1 first_year_cnt, 
    date_format(d2, '%Y-%m') second_year_mon, 
    c2 second_yeat_cnt
from(
    select job, date d1, sum(num) c1
    from resume_info
    where year(date) = 2025
    group by month(date), job) a
join(
    select job, date d2, sum(num) c2
    from resume_info
    where year(date) = 2026
    group by month(date), job) b
on a.job = b.job
and month(d1) = month(d2)
order by first_year_mon desc, job desc;

第88题 最差是第几名(二)

# 开窗函数
SELECT grade FROM 
(SELECT grade,
 SUM(number) over(ORDER BY grade) AS a, --  按照正序来排，各等级最差名次（最差正数第几名）
 SUM(number) over(ORDER BY grade DESC ) AS b,  -- 按照倒序来排，各等级最差名次（最好倒数第几名：比如A为12，即如果我得A,我的最优名次为倒数第12名，即正数第1名）
 (SELECT SUM(number) FROM class_grade)  AS total  -- 考生总数
 FROM class_grade) t1  -- 需要用临时表
 WHERE a >= total/2 AND b >=total/2
 ORDER BY grade; -- 按等级排序

另一种解法，找中位数位置和左右边界。

-- 第1张表
with t1 as
(
select grade
      ,number
      ,sum(number) over(order by grade)-number+1 as left_order
      ,sum(number) over(order by grade)          as right_order
  from class_grade
 order by grade
)
-- 第2张表
, t2 as
(
select floor((sum(number)+1)/2) as mid
  from class_grade
union
select ceil((sum(number)+1)/2) as mid
  from class_grade
)
-- 3. 找到中位数对应的等级
select distinct grade
  from t1,t2
 where t2.mid between left_order and right_order

与解法2类似，但左右边界数值不一样

select grade from
(
select *, (tb1.ed - tb1.number) st, sum(number) over() tp 
	from 
	(select *, sum(number) over(order by grade) ed from class_grade) tb1
) tb2
where tp/2 between st and ed;

解法3：通解法1，表联结

select t1.grade
FROM
(
select grade,
       sum(number) over(order by grade)-number+1 left_rank,
       sum(number) over(order by grade) right_rank
from class_grade
) t1,
(
select floor((sum(number)+1)/2) xia, ceil((sum(number)+1)/2) shang
from class_grade
) t2
where xia>=left_rank and xia<=right_rank
        or shang>=left_rank and shang<=right_rank
order by grade;