牛客网MySQL答案整理
入门题
(1) SQL1 寻找最晚入职员工的所有信息
select * from employees where hire_date=(select distinct hire_date from employees
order by hire_date desc limit 1 offset 2);
简单题
(1) SQL2 查找employees里入职员工时间排名倒数第三的员工所有信息
# 注意执行顺序是先select然后再order by
select * from employees where hire_date=(select distinct hire_date from employees
order by hire_date desc limit 1 offset 2);
(2) SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no
select e.last_name,e.first_name,d.dept_no from
employees e inner join dept_emp d on
e.emp_no=d.emp_no;
(3) SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
select emp_no,count(salary) t from salaries
group by emp_no
having count(salary)>15;
(4) SQL8 找出所有员工具体的薪水salary情况
select distinct salary from salaries order by salary desc;
(5) SQL10 找出所有非部门领导的员工emp_no
-- 方法1
select e.emp_no from
employees e left join dept_manager d on
e.emp_no=d.emp_no
where d.emp_no is null;
-- 方法2
select e.emp_no from employees e
where e.emp_no not in
(select distinct emp_no from dept_manager);
(6) SQL15 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息
select * from employees where emp_no&1 and last_name != 'Mary'
order by hire_date desc;
(7) SQL 17 获取薪水第二多的员工的emp_no以及其对应的薪水salary
-- 方法1
select emp_no,max(salary) from salaries where
salary<(select max(salary) from salaries);
-- 方法2
select emp_no,salary from salaries group by salary
order by salary desc limit 1 offset 1;
(8)SQL32 将employees表的所有员工的last_name和first_name拼接起来作为Name
select concat(last_name,' ',first_name) Name from employees;
(9) 批量插入数据
```sql
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');
(10) SQL42 删除emp_no重复的记录，只保留最小的id对应的记录
- 注意不能直接对同一张表进行删除和取数，需要将取数的结果视为另外一张表t
delete from titles_test where id not in
(select * from (select min(id) from titles_test group by ) as t)
(11) SQL43 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01
update titles_test set to_date = NULL, from_date = "2001-01-01"
where id in
(
select * from (
select id
from titles_test
where to_date = "9999-01-01"
) as t
);
-- 注意,此处最好不涉及多表查询,注意中间是','(不要用and)
update titles_test set to_date = NULL,from_date = "2001-01-01"
where to_date = "9999-01-01";
(12) SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错
MySQL中replace into 有三种形式
- replace into tbl_name(col_name, …) values(…)
- replace into tbl_name(col_name, …) select …
- replace into tbl_name set col_name=value, …
其中前两种对应于insert,后面一种对应update(不过update是update table set col_name=’…’)
-- (1)
REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
-- (2)
EPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
REPLACE INTO titles_test
SELECT 5,10005,title,from_date,to_date FROM titles_test
WHERE id=5 AND emp_no=10001;
--(3)
update titles_test set emp_no=replace(emp_no,'10001','10005');
(13) SQL45 将titles_test表名修改为titles_2017
alter table titles_test rename to titles_2017;
(14) SQL62 出现3次以上积分相同的情况
select number from grade group by number having count(id)>=3
(15) SQL64 找到每个人的任务
select p.*,t.content from
person p left join task t on
p.id=t.person_id;
(16) SQL66 牛客每个人最近的登陆日期(一)
select user_id,max(date) from login group by user_id
order by user_id;
(17) SQL72 考试分数(一)
select job,round(avg(score),3) avg from grade group by job
order by avg desc;
(18) SQL84 实习广场投递简历
-- count()是对行数进行求和,而sum()则是普通的求和函数
select job,sum(num) as cnt from resume_info
where date<='2025-12-31' and date>='2025-01-01'
group by job
order by cnt desc;
select job,sum(num) as cnt from resume_info
where date like '2025%'
group by job
order by cnt desc;
select job,sum(num) cnt
from resume_info
where date between '2025-01-01' and '2025-12-31'
group by job
order by cnt desc;
中等题
(1) SQL3 查找各个部门当前领导的薪水详情以及其对应部门编号dept_no
select s.*,dm.dept_no from salaries s
inner join dept_manager dm on
s.emp_no=dm.emp_no
order by s.emp_no asc;
(2) SQL5 查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工
select e.last_name,e.first_name,de.dept_no from employees e
left join dept_emp de on
e.emp_no=de.emp_no;
(3) SQL11 获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示
select de.emp_no,dm.emp_no from dept_emp de
inner join dept_manager dm on
de.dept_no=dm.dept_no
where de.emp_no not in
(select distinct emp_no from dept_manager);
(4) SQL 16 统计出各个title类型对应的员工薪水对应的平均工资avg
select t.title,avg(s.salary) from titles t
inner join salaries s on
t.emp_no=s.emp_no
group by title
order by avg(s.salary);
(5) SQL19 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name from
employees e left join dept_emp de on
e.emp_no=de.emp_no
left join departments d on
de.dept_no=d.dept_no;
(6) SQL 22 统计各个部门的工资记录数
select d.dept_no,d.dept_name,count(salary) from
departments d inner join dept_emp de on
d.dept_no=de.dept_no
inner join salaries s on
de.emp_no=s.emp_no
group by d.dept_no
order by d.dept_no asc;
(7) SQL 29 使用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.film_id is null;
select f.film_id,f.title from film f
left join film_category fc on
f.film_id=fc.film_id
where f.film_id not in
(select distinct film_id from film_category);
(8) SQL 30 使用子查询的方式找出属于Action分类的所有电影对应的title,description
-- 非子查询方式
select f.title,f.description from
film f left join film_category fc on f.film_id=fc.film_id
left join category c on fc.category_id=c.category_id
where c.name = 'Action';
-- 子查询方式
select f.title,f.description from film f
where f.film_id in
(select fc.film_id from film_category fc inner join category c on
fc.category_id=c.category_id where c.name='Action'
);
(9) SQL 33 创建一个actor表
create table if not exists actor(
actor_id smallint(5) not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null);
(10) 批量插入数据,不使用replace
# 注意忽略其中已经存在的数据
insert ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33');
(11) SQL 36 创建一个actor_name表
-- 方法1：先建表再插入
create table if not exists actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null);
insert into actor_name
select first_name,last_name from actor;
-- 方法2：在建表的同时进行插入
create table actor_name
select first_name,last_name from actor;
(12) SQL 37 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
(13) SQL 38 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v
create view actor_name_view as
select first_name first_name_v,last_name last_name_v from actor;
(14) SQL 39 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引
# 注意其中的()不能少
select * from salaries
force index (idx_emp_no)
where emp_no=10005;
(15) SQL 40 在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’
alter table actor add create_date
datetime not null default '2020-10-01 00:00:00';
(16) SQL 41 构建一个触发器
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
(17) SQL 46 在audit表上创建外键约束，其emp_no对应employees_test表的主键id
alter table audit add constraint c1 foreign key(emp_no)
references employees_test(ID);
alter table audit add foreign key(emp_no)
references employees_test(ID);
(18) SQl 48 将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%
update salaries set salary=1.1*salary
where emp_no in
(select emp_no from emp_bonus
where to_date='9999-01-01');
(19) SQL 50 将employees表中的所有员工的last_name和first_name通过(’)连接起来
select concat(last_name,"'",first_name) from employees;
(20) SQL 51 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt
select length('10,A,B')-length(replace('10,A,B',',','')) as cnt;
select char_length('10,A,B')-char_length(replace('10,A,B',',','')) as cnt;
(21) SQL 52 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
# -2代表从倒数第2个开始,2代表截取两个字符
select first_name from employees
order by substr(first_name,-2,2) asc;
(22) SQL 53 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no) employees from
dept_emp group by dept_no;
(23) SQL 53 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no) employees from dept_emp group by dept_no;
(24) SQL 54 查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary
select avg(salary) avg_salary from salaries
where 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')
and to_date='9999-01-01';
select sum(salary)/count(*) from salaries
where salary != (select min(salary) from salaries)
and salary != (select max(salary) from salaries)
and to_date='9999-01-01';
select (sum(salary)-min(salary)-max(salary))/(count(emp_no)-2) from salaries
where to_date='9999-01-01';
(25) SQL 55 分页查询employees表，每5行一页，返回第2页的数据
select * from employees limit 5 offset 5;
# order by emp_no;
-- 先写order by,然后进行limit
select * from employees
limit 5 offset 5;
(26) SQL 56 使用含有关键字exists查找未分配具体部门的员工的所有信息
使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是：select * from students. 然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立(这与关联子查询的思路是一致的)。如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回
select * from employees where not exists
# 首先得到父查询中的emp_no
# 然后带入子查询判断emp_no是否存在于dept_emp中——也就是是否已经分配好了部门
(select emp_no from dept_emp where employees.emp_no=dept_emp.emp_no);
-- 等价查询
select * from employees where emp_no not in
(select emp_no from dept_emp);
(27)SQL 63 刷题通过的题目排名
select id,number,dense_rank() over(order by number desc)
as t_rank from passing_number
order by number desc,id asc;
(28) SQL 73 考试分数(二)
-- 用两个select实现
select * from grade
where score >
(select avg(score) from grade g1 where g1.job = grade.job)
order by id;
-- 将job及对应的平均数建立一个表，然后与原表进行连接
select g.id, g.job,score
from grade g join (select job, avg(score) avg1 from grade group by job)s
on g.job = s.job
where score > avg1
order by id;
select * from grade
where score>(select avg(score) from grade g1 where g1.job=grade.job)
order by id;
select * from grade g1
inner join (select job,avg(score) avg_score from grade g2 group by job) t
where g1.job=t.job
and g1.score>t.avg_score
order by id;
(29) SQL 78 牛客的课程订单分析(二) *
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序
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(*)>1
order by user_id asc;
(30) SQL 79 牛客的课程订单分析(三) *
写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序
# 注意在使用group by之后使用select * 只能得到部分数据
select * from order_info
where user_id in
(select user_id from order_info o1
where product_name in ('C++','Python','Java')
and status='completed'
and date>'2025-10-15'
group by user_id
having count(user_id)>=2)
and product_name in ('C++','Python','Java')
and status='completed'
and date>'2025-10-15';
(31) SQL 82 牛客的课程订单分析(六) *
# 该题需要注意order_info中部分client_id在client中是不存在的,为了突出id的不存在采用left join而非inner join
select o.id,o.is_group_buy,
(case when o.is_group_buy='Yes' then NULL
else c.name end) client_name
from order_info o left join client c on
o.client_id=c.id
where o.user_id in
(select user_id from order_info
where date>'2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed'
group by user_id
having count(*)>=2)
and date>'2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed';
select o.id,o.is_group_buy,c.name client_name from
order_info o left join client c on
o.client_id=c.id where user_id in
(select user_id from order_info
where status='completed'
and date>'2025-10-15'
and product_name in ('C++','Python','Java')
group by user_id
having count(user_id)>=2)
and status='completed'
and date>'2025-10-15'
and product_name in ('C++','Python','Java');
(32) SQL 85 实习广场投递简历分析(二) *
select job,date_format(date,'%Y-%m') mon,
sum(num) cnt from resume_info
where date_format(date,'%Y')='2025'
group by job,mon
order by mon desc,cnt desc;
(33) SQL 87 最差是第几名 *
select grade,sum(number) over(order by grade asc) t_rank from class_grade;
(34) 获得积分最多的人 *
首先找到积分最多的人以及其对应的id，然后与user表进行连接
select u.name,t.sumnum from user u
inner join
(select user_id,sum(grade_num) sumnum from grade_info
group by user_id
order by sumnum desc
limit 1 offset 0) t
on u.id=t.user_id;
select distinct u.name,sum(grade_num)
over(partition by g.user_id) grade_sum from
user u inner join grade_info g on
u.id=g.user_id
order by grade_sum desc
limit 1 offset 0;
较难
(1) SQL 18 查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
# 从salaries中进行选取，限制salary<max(salary)
select e.emp_no,s.salary,e.last_name,e.first_name from
employees e inner join salaries s on
e.emp_no=s.emp_no
where s.salary=
(select max(salary) from salaries where
salary<(select max(salary) from salaries));
# 从删除最大salary的salaries中进行选择
select e.emp_no,s.salary,e.last_name,e.first_name from
employees e inner join salaries s on
e.emp_no=s.emp_no
where s.salary=
(select max(salary) from
(select salary from salaries where salary<
(select max(salary) from salaries)) t );
(2) SQL 23 对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列：
select emp_no,salary,dense_rank() over(order by salary desc) t_rank
from salaries
order by salary desc,emp_no asc;
(3) SQL 24 获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary:
select de.dept_no,de.emp_no,s.salary from
dept_emp de inner join salaries s on
de.emp_no=s.emp_no
left join dept_manager dm on
de.emp_no=dm.emp_no
where dm.emp_no is null;
select de.dept_no,de.emp_no,s.salary from
dept_emp de inner join salaries s on
de.emp_no=s.emp_no
where de.emp_no not in
(select emp_no from dept_manager);
select de.dept_no,de.emp_no,s.salary from
dept_emp de inner join salaries s on
de.emp_no=s.emp_no
where not exists
(select dm.emp_no from dept_manager dm
where de.emp_no=dm.emp_no);
(4) SQL 59 获取有奖金的员工相关信息
select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
(case eb.btype
when 1 then 0.1*s.salary
when 2 then 0.2*s.salary
else 0.3*s.salary
end) bonus
from employees e inner join emp_bonus eb
on e.emp_no=eb.emp_no
inner join salaries s
on e.emp_no=s.emp_no
where to_date='9999-01-01';
(5) SQL 60 统计salary的累计和running_total *
select emp_no,salary,
sum(salary) over(order by emp_no) running_total
from salaries
where to_date = '9999-01-01';
【注】:(1) 当使用窗口函数的时候所选的字段可以自动匹配sum的结果(但是不会进行去重,需要去重的时候可以添加distinct,且需要注意的是最后的结果数目与原始表格的记录数是一样的),以login表为例,我们计算对于user_id的加和(也就是sum)，加和的顺序按照client_id进行排序：
select id,client_id,sum(user_id) over(order by client_id) from login;
可以看出id,user_id和sum()的结果实际上是按照user_id进行同步排序的
(2) 使用聚合函数的时候则不会有相同的结果,最后得到1|1|10
(6) SQL 61 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name **
括号中的子查询是查找到first_name<=e1.first_name的人数，如果是奇数就输出此时的e1.first_name
select first_name from employees e1
where (select count(*) from employees e2
where e2.first_name<=e1.first_name)%2=1;
select first_name from
(select emp_no,first_name,row_number() over(order by first_name asc) t_rank
from employees) t
where t.t_rank%2=1
order by t.emp_no asc; # row_number()中order by打乱了原来的顺序
# 注意以下写法是错误的：在使用聚合函数的时候要么通过关联子查询进行限制，要么就通过group by来限制分组
select first_name from
(select first_name,count(*) as cnt from employees
where count(*)%2=1
order by first_name asc) t;
(7) SQL65 异常的邮件概率 **
# select e.date,round(sum(case e.type
# when "no_completed" then 1 else 0 end)/count(*),3) p
# from email e
# inner join user u1 on e.send_id = u1.id
# inner join user u2 on e.receive_id = u2.id
# where u1.is_blacklist = 0 and u2.is_blacklist = 0
# group by e.date
# order by date;
# select date,round(avg(case when type='no_completed' then 1 else 0 end),3) 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
# order by date;
# 基本思想：将send_id和receive_id分别限制为正常用户然后计算其中正常用户的比例
select date,round(avg(case type
when 'no_completed' then 1
else 0
end),3) p from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
group by date
order by date asc ;
select date,round(sum(case type
when 'no_completed' then 1
else 0 end)/count(*),3) p
from email e inner join user u1
on e.send_id=u1.id
inner join user u2
on e.receive_id=u2.id
where u1.is_blacklist=0
and u2.is_blacklist=0
group by date
order by date asc;
(8) SQL 67 牛客每个人最近的登陆日期(二) *
注意，在进行select的时候如果同时select多个字段，需要保证select的条件是一致的：在采用group by的时候只会根据group的对象将数据进行分组来进行筛选，采用聚合函数的时候并不会同步去考虑其他的字段，如下图可以看出选择max(date)的时候并没有同步考虑u_n和c_n:
如果group by中出现group by则此时聚合的结果和对应的group by是一致的：
但是该方法仅限于group by一个分组的时候,如下图对c_n,u_n进行group by的结果是3条记录：
-- 一种错误的写法
select u.name u_n,c.name c_n,max(l.date) date from login l
inner join user u on
l.user_id=u.id
inner join client c on
l.client_id=c.id
group by u.name,l.date
order by u.name asc;
-- 正确的写法
select u.name u_n, c.name c_n, date
from login l
left join user u on l.user_id = u.id
left join client 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)
order by u_n;
(9) SQL 68 牛客每个人最近的登陆日期(三) *
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率:
# # 留存率:第二天登录人数/第一天登陆人数
select round(count(distinct user_id)/(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);
(10) SQL 69 牛客每个人最近的登陆日期(四) ***(very important)
写一个sql语句查询每个日期登录新用户个数，并查询结果按照日期升序排序
# 次日留存率
select a.date,count(b.user_id) 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;
select a.date,sum(case when t_rank=1 then 1 else 0 end) new
from (select date,row_number() over(partition by user_id
order by date)
t_rank from login) a
group by date;
(11) SQL 71 牛客每个人最近的登陆日期(六) ***
牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0
此处计算累计通过数目
#因为有登录没有刷题的数据不需要输出，所以截止到某天指的是刷题表有记录的日期。没有用到登录表
select name as u_n,date,
sum(number) over(partition by user_id order by date) as ps_num
from passing_number p
inner join user u
on p.user_id=u.id
order by date,u_n;
select u.name u_n,p.date date,
sum(p.number) over(partition by p.user_id order by p.date) ps_num
from passing_number p
inner join user u
on p.user_id=u.id
inner join login l
# 为什么在进行多表连接的时候不能直接采用以下注释的表达式？
# on p.user_id=l.user_id / p.user_id=l.user_id
# 原因在于如果仅使用用户id，同一个用户可能在不同的日子里登录
# 在进行连接的时候必须确保同一个用户在同一天登录并通过提交
on p.date=l.date and p.user_id=l.user_id
order by p.date,u.name;
【注】:多表连接，感觉结果相同，实际结果不同
(12) SQL 74 考试分数(三) ****
找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
关联子查询的执行顺序：https://zhuanlan.zhihu.com/p/41844742
# 采用关联子查询:首先select g1.score放入子查询语句中
# 然后判断g2.score>=g1.score的个数
select g1.id, l.name, g1.score
from grade g1 join language l on g1.language_id=l.id
where
(select count(distinct g2.score) from grade g2
# 将grade中分数小于前两名的筛选掉
where g2.score>=g1.score and g1.language_id=g2.language_id
) <=2 order by l.name asc,g1.score desc ,g1.id asc;
SELECT a.id,name,score
FROM (
SELECT id,language_id,score,
DENSE_RANK() OVER (PARTITION BY language_id ORDER BY score DESC) AS r
FROM grade) AS a
INNER JOIN language
ON a.language_id=language.id
WHERE r<=2
ORDER BY name,score DESC,a.id
【注】：Top N问题：
(1) 寻找最小值
(2) 在分组不多的时候寻找各组TopN
(3) 在分组较多的时候寻找各组TopN
(13) SQL75 考试分数(四) *
SELECT job,
floor(( count(*) + 1 )/ 2 ) AS start,
floor(( count(*) + 2 )/ 2 ) AS end
FROM grade
GROUP BY job
ORDER BY job
(14) SQL80 牛客的课程订单分析(四)
写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序
该题只能算中等难度…
select user_id,min(date) first_buy_date,count(user_id) cnt from
order_info where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Java','Python')
group by user_id
having cnt>=2
order by user_id;
(15) SQL 83 牛客的课程订单分析(七) ***
写出一个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;
(16) SQl 88 最差是第几名(二) ***
【注】:该题主要考察中位数的计算
select grade from
(select grade,
(select sum(number) from class_grade) as total,
sum(number) over(order by grade) a,
sum(number) over(order by grade desc) b
from class_grade
order by grade) t
where a>=total/2 and b>=total/2
order by grade;
# 等价的式子
with t_rank as
(
select grade,
(select sum(number) from class_grade) as total,
sum(number) over(order by grade) a,
sum(number) over(order by grade desc) b
from class_grade
order by grade
)
select grade
from t_rank
where a>=total/2 and b>=total/2
order by grade
(17) SQL 83 牛客网的订单分析 ***
写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序:
-- 方法1:
select (case when o.is_group_buy='Yes' then 'GroupBuy'
else c.name end) source,count(*) cnt
# 注意这里有部分用户是团购的,在client中找不到对应的id,因此需要采用left join
from order_info o left join client c
on o.client_id=c.id
# 查询满足条件的用户id
where o.user_id
in (select user_id from order_info
where product_name in ('C++','Java','Python')
and status='completed'
and date>'2025-10-15'
group by user_id
having count(*)>=2)
and o.product_name in ('C++','Java','Python')
and o.status='completed'
and o.date>'2025-10-15'
group by source # 注意最后是根据客户端进行分组统计
order by source;
-- 方法2:
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
(18) SQL 90 积分最多的人(二)
select t.user_id,u.name,t.grade_sum from
user u inner join
(select user_id,sum(grade_num) grade_sum from grade_info group by user_id) as t
on t.user_id=u.id
where t.grade_sum=(select max(grade_sum) from (select user_id,sum(grade_num) grade_sum from grade_info group by user_id) t1)
order by t.grade_sum desc,t.user_id asc;
select t.user_id,u.name,t.grade_sum from
(select distinct user_id,sum(grade_num) over(partition by user_id) grade_sum
from grade_info) t
inner join user u on t.user_id=u.id
where t.grade_sum=
(select max(grade_sum) from
(select sum(grade_num) over(partition by user_id) grade_sum
from grade_info) t1);
困难题
(1) SQl 12 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列：
select de.dept_no,de.emp_no,s.salary
from dept_emp de inner join salaries s on
de.emp_no=s.emp_no
where de.to_date='9999-01-01' and s.to_date='9999-01-01'
and s.salary in
# 获取同一部门的最大薪资
(select max(salary) from salaries s1
inner join dept_emp de1
on s1.emp_no=de1.emp_no
where de1.to_date='9999-01-01' and s1.to_date='9999-01-01'
and de1.dept_no=de.dept_no)
order by de.dept_no;
(2) SQL 21 查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序:
select e.emp_no,(s2.salary-s1.salary) growth from
employees e inner join salaries s1 on
e.emp_no=s1.emp_no and e.hire_date=s1.from_date
inner join salaries s2 on
e.emp_no=s2.emp_no and s2.to_date='9999-01-01'
order by growth asc;
(3) SQL 25 获取员工其当前的薪水比其manager当前薪水还高的相关信息:
select t1.emp_no,t2.emp_no,t1.salary,t2.salary from
# 查找员工的工资
(select de.emp_no,de.dept_no,s.salary from
dept_emp de inner join salaries s on
de.emp_no=s.emp_no) t1
inner join
# 查找部门领导的工资
(select dm.emp_no,dm.dept_no,s.salary from
dept_manager dm inner join salaries s on
dm.emp_no=s.emp_no) t2
on t1.dept_no=t2.dept_no
where t1.salary>t2.salary;
(4) SQL 26 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序：
# 寻找每个部门员工分配的title类型
select t.dept_no,d.dept_name,t.title,t.cnt from
(select de.dept_no,t.title,count(*) cnt from dept_emp de
inner join titles t on
de.emp_no=t.emp_no
group by de.dept_no,t.title) t
inner join departments d on
t.dept_no=d.dept_no
order by dept_no asc,t.title;
(5) SQL 28 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部：
select c.name,count(f.film_id) from film f
inner join film_category fc on f.film_id=fc.film_id
inner join category c on fc.category_id=c.category_id
where f.description like '%robot%' # 找到包含robot的电影信息
and c.category_id in
(select category_id from
film_category group by category_id
having count(film_id)>=5)
(6) SQL 70 牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率：
select a.date, round(count(b.user_id) *1.0/count(a.user_id),3) as p
FROM (select user_id, min(date) as date
from login
group by user_id) a
left join login b
on a.user_id=b.user_id
and b.date=date_add(a.date,interval 1 day)
group by a.date
union
select date ,0.000 as p
from login
where date not in (
select min(date) from login group by user_id)
order by date
(7) SQL 76 考试分数(五)
写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序:
select id,job,score,s_rank
from
(select *
,(row_number()over(partition by job order by score desc))as s_rank
,(count(score)over(partition by job))as num
from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;
(8) SQL 81 牛客的课程订单分析(五)
select user_id,
min(date) as first_buy_date,
min(second_buy_date)as second_buy_date,
count(*) as cnt
from
# 得到符合条件的用户id、第一次购买时间以及第二次购买时间
(select user_id,date,
lead(date,1) over(partition by user_id order by date)as second_buy_date
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed') t
group by user_id
having count(*)>=2
order by user_id;
(9) SQL 86 写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示：
select t1.job first_year_mon,t1.mon first_year_mon,t1.cnt first_year_cnt,
t2.mon second_year_mon,t2.cnt second_year_cnt from
(select job,date_format(date,'%Y-%m') mon,sum(num) cnt from resume_info r
where date>'2025-01-01' and date<'2025-12-31'
group by job,mon) t1
inner join
(select job,date_format(date,'%Y-%m') mon,sum(num) cnt from resume_info r
where date>'2026-01-01' and date<'2026-12-31'
group by job,mon)t2
on t1.job=t2.job
and right(t1.mon,2)=right(t2.mon,2)
order by t1.mon desc,t1.job desc;
(9) SQL 91 获得积分最多的人(三)
select u.id,u.name,t.grade_sum from
user u inner join
(select user_id,sum(
case when type='add' then grade_num
else -grade_num
end) grade_sum from grade_info
group by user_id) t
on u.id=t.user_id
where t.grade_sum=
(select max(grade_sum) from
(select user_id,sum(
case when type='add' then grade_num
else -grade_num
end) grade_sum from grade_info
group by user_id) t1)
order by id;
# 首先计算所有用户的积分
with t as
(select u.id,u.name,g.user_id,
sum(case when g.type='add' then grade_num
else -grade_num end) grade_sum
from user u inner join grade_info g
on u.id=g.user_id
group by u.id)
# 然后选取出积分最高的用户信息
select u.id,u.name,t.grade_sum
from user u inner join t
on u.id=t.user_id
and t.grade_sum=(select max(grade_sum) from t);
【注】:date_add(date,interval 1 day)与lead(date) over(partition by … order by…)区别：前者是简单的时间相加，而后者则是将序列向前进行移动(也就是取得下一天的值)：
扫一扫
1438
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
