牛客网mysql困难类题目自写答案

最近做了一下牛客网困难部分的题目，打一下卡

#牛客12
select * from dept_emp;
select * from salaries;

select m.dept_no,m.emp_no,m.salary maxSalary from
(select rank() over(partition by a.dept_no ORDER BY b.salary DESC) dd,a.dept_no,a.emp_no,b.salary from dept_emp a,salaries b where a.emp_no =b.emp_no ) m
where m.dd=1 ORDER BY m.dept_no;

#牛客21
select * from employees;
select * from salaries;

select m.emp_no,m.growth from
(select a.emp_no,b.salary-a.salary growth,b.to_date from salaries a,salaries b,employees c where a.emp_no=b.emp_no and a.emp_no=c.emp_no and c.hire_date=a.from_date having DATE_FORMAT(b.to_date,'%Y')='9999') m ORDER BY m.growth

#牛客25
select * from dept_emp;
select * from dept_manager;
select * from salaries;

select a.emp_no,b.emp_no e,c.salary,d.salary f from dept_emp a,dept_manager b,salaries c,salaries d
where a.dept_no=b.dept_no and a.emp_no=c.emp_no and b.emp_no=d.emp_no and c.salary>d.salary

#牛客26
select * from departments;
select * from dept_emp;
select * from titles;

select a.dept_no,a.dept_name,c.title,count(*) from departments a,dept_emp b,titles c
where a.dept_no=b.dept_no and b.emp_no=c.emp_no GROUP BY a.dept_name,c.title,a.dept_no ORDER BY a.dept_no

#牛客28
select * from film;
select * from category;
select * from film_category;

select i.name,j.ddd from
(select m.name,m.category_id from 
(select a.name,count(*) s,a.category_id from category a,film_category b where a.category_id=b.category_id and a.category_id in (select category_id from film_category where film_id in (select film_id from film where description like '%robot%') ) GROUP BY a.name,a.category_id ) m  where m.s>=5)  i,
(select b.category_id,count(*) ddd from film a,film_category b where a.film_id=b.film_id and description like '%robot%' GROUP BY b.category_id) j
where i.category_id=j.category_id

#牛客70
select * from login;

select z.date,FORMAT(ifnull(gg.s,0),3) p from 
(select date from login GROUP BY date) z left join
(select m.date,n.id/m.id s from 
(select a.date,count(DISTINCT a.user_id) id from 
(select rank() over(PARTITION by user_id ORDER BY date) as ran,date,user_id from login) a where a.ran=1 GROUP BY a.date) m,
(select a.date,count(DISTINCT a.user_id) id from 
(select rank() over(PARTITION by user_id ORDER BY date) as ran,date,user_id from login) a,(select rank() over(PARTITION by user_id ORDER BY date) as ran,date,user_id from login) b where a.date=b.date-1 and a.user_id=b.user_id and a.ran=1 GROUP BY a.date) n
where m.date=n.date ) gg 
on z.date=gg.date ORDER BY z.date

#牛客76
select * from grade;

select b.id,b.job,b.score,b.ran t_rank from 
(select job,FORMAT((count(job)+1)/2,1) zhong from grade GROUP BY job) a,
(select ROW_NUMBER() over(PARTITION by job ORDER BY score desc) ran,id,job,score from grade) b 
where a.job=b.job and (a.zhong=b.ran or a.zhong=b.ran+0.5 or a.zhong=b.ran-0.5) ORDER BY b.id

#牛客81
select * from order_info;

select m.user_id,m.first_buy_date,n.date second_buy_date,m.cnt from
(select user_id,min(date) first_buy_date,count(*) cnt from order_info where date>'2025-10-15' and status='completed' and (product_name in ('C++','Python','Java')) GROUP BY user_id HAVING cnt>=2) m,
(select user_id,date,ROW_NUMBER() over(PARTITION by user_id order by date) tt from order_info where date>'2025-10-15' and status='completed' and (product_name in ('C++','Python','Java'))) n
where m.user_id=n.user_id and n.tt=2 ORDER BY m.user_id

#牛客86
select * from resume_info;

select a.job,DATE_FORMAT(a.date,'%Y-%m') first_year_mon,convert(sum(a.num)/count(DISTINCT a.job,a.date),decimal(12,0)),DATE_FORMAT(b.date,'%Y-%m') first_year,convert(sum(b.num)/count(DISTINCT b.job,b.date),decimal(12,0)) from resume_info a,resume_info b where a.job=b.job and DATE_FORMAT(DATE_ADD(a.date,INTERVAL 1 year),'%Y-%m')=DATE_FORMAT(b.date,'%Y-%m') and DATE_FORMAT(a.date,'%Y')='2025' GROUP BY a.job,DATE_FORMAT(a.date,'%Y-%m'),b.job,DATE_FORMAT(b.date,'%Y-%m') order by first_year_mon desc,a.job desc

select a.*,b.* from resume_info a,resume_info b where a.job=b.job and DATE_FORMAT(DATE_ADD(a.date,INTERVAL 1 year),'%Y-%m')=DATE_FORMAT(b.date,'%Y-%m') and DATE_FORMAT(a.date,'%Y')='2025'