牛客sql_全部题目

SQL1查找最晚入职员工的所有信息

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

SQL2查找入职员工时间排名倒数第三的员工所有信息

select 
    emp_no
	,birth_date
	,first_name
	,last_name
	,gender
    ,hire_date 
from (
    select 
        *
        ,dense_rank() over(order by hire_date desc) rk
    from employees
) t
where rk=3

SQL3查找当前薪水详情以及部门编号dept_no

select emp_no,salary,from_date,dept_manager.to_date,dept_no
from dept_manager
left join salaries using(emp_no)

SQL4查找所有已经分配部门的员工的last_name和first_name以及dept_no

select 
    last_name 
	,first_name 
	,dept_emp.dept_no
from dept_emp
left join employees using(emp_no )

SQL5查找所有员工的last_name和first_name以及对应部门编号dept_no

select 
    last_name 
	,first_name 
	,dept_emp.dept_no
from dept_emp
right join employees using(emp_no )

SQL7查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t>15

SQL8找出所有员工当前薪水salary情况

select distinct salary
from salaries
order by salary desc

SQL10获取所有非manager的员工emp_no

select emp_no
from employees
where emp_no not in (select emp_no from dept_manager)

SQL11获取所有员工当前的manager

select dept_emp.emp_no,dept_manager.emp_no as manager 
from dept_emp
left join dept_manager using(dept_no )
where dept_emp.emp_no != dept_manager.emp_no

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

select       dept_no
            ,emp_no
            ,maxSalary
from (
    select   dept_no
            ,emp_no
            ,maxSalary
            ,row_number() over(partition by dept_no order by maxSalary desc) rk
    from (
        select 
            dept_no
            ,dept_emp.emp_no
            ,max(salary) as maxSalary
        from dept_emp
        left join salaries using(emp_no)
        where dept_emp.to_date='9999-01-01'
        group by dept_no,dept_emp.emp_no
    ) t
) t2
where rk=1

SQL15查找employees表emp_no与last_name的员工信息

select *
from employees
where emp_no%2=1 and last_name != 'Mary'
order by hire_date desc 

SQL16统计出当前各个title类型对应的员工当前薪水对应的平均工资

select title,avg(salary) 
from titles
left join salaries using(emp_no)
group by title

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

select 
    emp_no 
	,salary
from (
    select 
        emp_no
        ,salary
        ,dense_rank() over(order by salary desc) rk
    from salaries
) t
where rk=2

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

select 
    emp_no 
	,salary
	,last_name
    ,first_name 
from employees
left join salaries using(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
)

SQL19查找所有员工的last_name和first_name以及对应的dept_name

select 
    last_name
    ,first_name
    ,dept_name 
from employees
left join dept_emp using(emp_no)
left join departments using(dept_no)

SQL21查找在职员工自入职以来的薪水涨幅情况

select a.emp_no,end_salary-begin_salary as growth
from (
    select emp_no,salary as end_salary
    from salaries
    where to_date='9999-01-01'
)a left join (
    select employees.emp_no,salary as begin_salary
    from employees
    left join salaries using(emp_no)
    where  hire_date=from_date 
)b using(emp_no)
order by growth asc

SQL22统计各个部门的工资记录数

select d.dept_no,dept_name,count(salary)
from departments d
left join dept_emp using(dept_no)
left join salaries using(emp_no)
group by d.dept_no,dept_name

SQL23对所有员工的薪水按照salary降序进行1-N的排名

select 
    emp_no
    ,salary
    ,dense_rank() over(order by salary desc) as t_rank 
from salaries
order by t_rank asc,emp_no asc;

SQL24获取所有非manager员工当前的薪水情况

select
    dept_no
	,emp_no
	,salary 
from employees
left join dept_emp using(emp_no)
left join salaries using(emp_no)
where  emp_no not in (select emp_no from dept_manager)

SQL25获取员工其当前的薪水比其manager当前薪水还高的相关信息

select 
    a.emp_no
    ,b.emp_no as manager_no
    ,a.salary as emp_salary
    ,b.salary as manager_salary
from (
    select dept_no,emp_no,salary
    from dept_emp
    left join salaries using(emp_no)
) a left join (
    select dept_no,emp_no,salary
    from dept_manager
    left join salaries using(emp_no)
) b using(dept_no)
where a.emp_no!=b.emp_no and a.salary>b.salary

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

select 
   departments.dept_no
	,dept_name
	,title
    ,count(title)  
from departments
left join dept_emp using(dept_no)
left join titles using(emp_no)
group by    departments.dept_no,dept_name,title
order by dept_no asc,title asc

SQL29使用join查询方式找出没有分类的电影id以及名称

select film_id,title
from film
left join film_category using(film_id)
left join category using(category_id)
where category_id is null

SQL30使用子查询的方式找出属于Action分类的所有电影对应的title,description

select title,description 
from film
left join film_category using(film_id)
left join category using(category_id)
where name='Action'

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

select concat(last_name," ",first_name)
from employees

SQL33创建一个actor表，包含如下列信息

create table actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null comment "名字",
last_name varchar(45) not null 	comment "姓氏",
last_update date not null comment "日期"	 
)

SQL34批量插入数据

insert into actor 
values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

SQL35批量插入数据，不使用replace操作

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

SQL36创建一个actor_name表

create table actor_name 
select first_name,last_name
from actor

SQL37对first_name创建唯一索引uniq_idx_firstname

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

SQL38针对actor表创建视图actor_name_view

create view actor_name_view as
select first_name as first_name_v,last_name as last_name_v
from actor

SQL39针对上面的salaries表emp_no字段创建索引idx_emp_no

select *
from salaries force index(idx_emp_no)
where emp_no=10005;

SQL40在last_update后面新增加一列名字为create_date

alter table actor
add column create_date datetime not null default '2020-10-01 00:00:00'
after last_update;  

SQL41构造一个触发器audit_log

create trigger audit_log after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end

SQL42删除emp_no重复的记录，只保留最小的id对应的记录。

delete from titles_test
where id not in(
    select * from (
        select min(id)
        from titles_test
        group by emp_no
        ) t
    );

SQL43将所有to_date为9999-01-01的全部更新为NULL

update titles_test 
set to_date=null,from_date = '2001-01-01'
where to_date='9999-01-01'

SQL44将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

replace into titles_test 
select 5,10005,title, from_date, to_date
from titles_test
where id=5;

SQL45将titles_test表名修改为titles_2017

alter table titles_test rename titles_2017;

SQL46在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit
add constraint foreign key (emp_no)
references employees_test(id)

SQL48将所有获取奖金的员工当前的薪水增加10%

update salaries
set salary=1.1*salary
where emp_no in (select emp_no
                from emp_bonus)
      and to_date='9999-01-01'

SQL50将employees表中的所有员工的last_name和first_name通过引号连接起来。

select concat(last_name,"'",first_name)
from employees

SQL51查找字符串中逗号出现的次数

select id,length(string)-length(replace(string,",",'')) as cnt
from strings

SQL52获取employees中的first_name

select first_name
from employees
order by right(first_name,2) asc

SQL53按照dept_no进行汇总

select dept_no,group_concat(emp_no separator ',')
from dept_emp
group by dept_no

SQL54平均工资

select avg(salary) as avg_salary 
from (
    select
        salary
        ,dense_rank() over(order by salary) rk1
        ,dense_rank() over(order by salary desc) rk2
    from salaries
    where to_date='9999-01-01'
) t
where t.rk1 != 1 and t.rk2 != 1

SQL55分页查询employees表，每5行一页，返回第2页的数据

select *
from employees
limit 5,5

SQL57使用含有关键字exists查找未分配具体部门的员工的所有信息。

select *
from employees
where not exists (select emp_no 
                  from dept_emp
                  where employees.emp_no=dept_emp.emp_no)

SQL59获取有奖金的员工相关信息。

select 
    emp_no
	,first_name
	,last_name
	,btype
	,salary
	,case 
     when btype=1 then 0.1*salary
     when btype=2 then 0.2*salary 
     else 0.3*salary
     end as bonus
from employees
join emp_bonus using(emp_no)
join salaries using(emp_no)
where to_date='9999-01-01'

SQL60统计salary的累计和running_total

select
    emp_no
    ,salary
    ,sum(salary) over(order by emp_no asc) as running_total
from salaries
where to_date = '9999-01-01'

SQL61给出employees表中排名为奇数行的first_name

select employees.first_name
from employees 
left join (select
               first_name
               ,row_number() over(order by first_name) rn
           from employees
) t1 using(first_name)
where t1.rn%2=1

SQL62出现三次以上相同积分的情况

select number
from grade
group by number
having count(number)>=3

SQL63刷题通过的题目排名

select
    id
    ,number
    ,dense_rank() over(order by number desc) as t_rank 
from passing_number

SQL64找到每个人的任务

select person.id,name,content 
from person
left join task on person.id=task.person_id

SQL65异常的邮件概率

select 
    date
    ,round(avg(case 
     when type='completed' then 0
     else 1
     end),3) as p 
from email
where send_id not in (select id from user where is_blacklist != 0)
and receive_id not in (select id from user where is_blacklist != 0)
group by date

SQL66牛客每个人最近的登录日期(一)

select user_id,max(date) as id
from login
group by user_id
order by user_id

SQL67牛客每个人最近的登录日期(二)

select         
      u_n
      ,c_n
      ,date
from(
    select 
        u.name as u_n
        ,c.name as c_n
        ,date
        ,row_number() over(partition by u.name order by date desc) rn
    from `user`u
    join login l on u.id=l.user_id
    join client c on c.id=l.client_id
) t 
where rn=1

SQL68牛客每个人最近的登录日期(三)

select round(avg(case when l.id is null then 0 else 1 end),3)
from(
    select 
        user_id
        ,min(date) as login_date
        ,date_add(min(date),interval 1 day) as login_2
    from login
    group by user_id
) t left join login l on l.user_id=t.user_id and l.date=t.login_2

SQL69牛客每个人最近的登录日期(四)

select 
        distinct login.date
        ,case when t2.num is null then 0 else t2.num end
from login
left join (
select login_date,count(login_date) as num
from(
    select user_id,min(date) as login_date
    from login
    group by user_id) t
group by login_date
) t2 on login.date=t2.login_date

SQL70牛客每个人最近的登录日期(五)

-- 0/0 为 null
select 
    date
    ,ifnull(round((sum(case 
    when 
    (user_id,date) in (select user_id,date_sub(date,interval 1 day) as date
    from login) 
    and 
    (user_id,date) in (select user_id,min(date)
    from login
    group by user_id)
    then 1 else 0
    end)/
    sum(case 
     when (user_id,date) in (select user_id,min(date)
     from login
     group by user_id)
     then 1 else 0
     end)),3),0)
from login 
group by date
order by date

SQL71牛客每个人最近的登录日期(六)

select
     u_n
     ,date
     ,sum(number) over(partition by u_n order by date asc) as ps_num
from (
    select 
        u.name as u_n
        ,l.date as date
        ,number
    from `user` u
    join login l on u.id=l.user_id
    join passing_number p on p.user_id=u.id 
    where l.date=p.date
) t
order by date,u_n  

SQL72考试分数(一)

select job,round(avg(score),3) as avg
from grade
group by job
order by avg desc;

SQL73考试分数(二)

select id,grade.job,grade.score 
from grade 
left join (
    select job,round(avg(score),3) as avg
    from grade
    group by job
) t using(job)
where grade.score>avg

SQL74考试分数(三)

select 
    id
    ,name
    ,score
from (
select 
    g.id as id 
    ,l.name as name
    ,score
    ,dense_rank() over(partition by language_id order by score desc) as rn
from grade g
left join `language` l on g.language_id=l.id
) t
where rn=1 or rn=2
order by name,score desc

SQL75考试分数(四)

select 
    job
    ,case when count(score)%2=1 then ceil(count(score)/2) else floor(count(score)/2) end as start
    ,case when count(score)%2=1 then ceil(count(score)/2) else floor(count(score)/2)+1 end as end
from grade
group by job
order by job

SQL76考试分数(五)

select id,a.job,a.score,rk as t_rank 
from(
select 
    id
    ,job
    ,score
    ,row_number() over(partition by job order by score desc) rk
from grade
) a  left join(
    select 
        job
        ,case when count(score)%2=1 then ceil(count(score)/2) else floor(count(score)/2) end as mid_num
    from grade
    group by job
    union     
    select 
        job
        ,case when count(score)%2=1 then ceil(count(score)/2) else floor(count(score)/2)+1 end as mid_num
    from grade
    group by job
) b on a.job=b.job 
where a.rk=b.mid_num
order by id

SQL77牛客的课程订单分析(一)

select *
from order_info
where date>'2025-10-15' and status='completed' and
product_name  in ('C++','Python','Java') 

SQL78牛客的课程订单分析(二)

select user_id
from order_info
where date>'2025-10-15' and status='completed' and
product_name  in ('C++','Python','Java') 
group by user_id
having count(status)>=2
order by user_id

SQL79牛客的课程订单分析(三)

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++','Python','Java') 
    group by user_id
    having count(status)>=2
) and date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 

SQL80牛客的课程订单分析(四)

select 
    user_id
    ,min(date)
    ,count(status)
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++','Python','Java') 
    group by user_id
    having count(status)>=2
) and date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 
group by user_id 
order by user_id

SQL81牛客的课程订单分析(五)

select user_id,date,lk,cnt
from(
    select 
        user_id
        ,date
        ,row_number() over(partition by user_id order by date asc) as rk
        ,lead(date,1) over(partition by user_id order by date asc) as lk
        ,count(status) over(partition by user_id) as cnt
    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++','Python','Java') 
        group by user_id
        having count(status)>=2
    ) and date>'2025-10-15' and status='completed' and
        product_name  in ('C++','Python','Java') 
) t
where rk=1
order by user_id

SQL82牛客的课程订单分析(六)

select o.id,is_group_buy,c.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 date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 
    group by user_id
    having count(status)>=2
) and date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 

SQL83牛客的课程订单分析(七)

select ifnull(c.name,'GroupBuy') as source,count(*)
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 date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 
    group by user_id
    having count(status)>=2
) and date>'2025-10-15' and status='completed' and
    product_name  in ('C++','Python','Java') 
group by c.name
order by source

SQL84实习广场投递简历分析(一)

select job,sum(num) as cnt 
from resume_info
where year(date)='2025'
group by job
order by cnt desc

SQL85实习广场投递简历分析(二)

select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt 
from resume_info
where year(date)='2025'
group by job,mon
order by mon desc,cnt desc

SQL86实习广场投递简历分析(三)

select a.job as job,a.mon as first_year_mon,a.cnt,b.mon,b.cnt
from(
    select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt 
    from resume_info
    where year(date)='2025'
    group by job,mon
    order by mon desc,cnt desc
) a join (
    select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt 
    from resume_info
    where year(date)='2026'
    group by job,mon
    order by mon desc,cnt desc
) b on a.job=b.job
where right(a.mon,2)=right(b.mon,2)
order by first_year_mon desc,job desc

SQL87最差是第几名(一)

select
    grade
    ,sum(number) over(order by grade) number
from class_grade

SQL88最差是第几名(二)

select grade
from(
    select
        grade
        ,(select sum(number) from class_grade) as total
        ,sum(number) over(order by grade) as a
        ,sum(number) over(order by grade desc) as b
    from class_grade
) t 
where a>=total/2 and b>=total/2
order by grade

SQL89获得积分最多的人(一)

select name,sum(grade_num) as grade_num
from `user` u
join grade_info i on u.id=i.user_id
group by name 
order by grade_num desc
limit 0,1

SQL90获得积分最多的人(二)

select user_id,name,grade_num
from(
    select user_id,name,sum(grade_num) as grade_num
    from `user` u
    join grade_info i on u.id=i.user_id
    group by user_id,name
) t 
where grade_num = (select max(a1)
                    from(
                        select user_id,sum(grade_num)  as a1
                        from grade_info
                        group by user_id
                    ) a
                    )

SQL91获得积分最多的人(三)

with temp as (
select 
    user_id
    ,name
    ,sum(case when type='add' then grade_num 
         else 0 end )-sum(case when type='reduce' then grade_num 
         else 0 end ) as grade_num
from `user` u
join grade_info i on u.id=i.user_id
group by user_id,name
)
select   
    user_id
    ,name
    ,grade_num
from temp
where grade_num = (select max(grade_num) from temp)

SQL92商品交易(网易校招笔试真题)

select goods_id,name,weight,total
from goods g
right join (
    select goods_id,sum(count) as total
    from trans
    group by goods_id
) t on g.id=t.goods_id
group by goods_id,name
having total>20 and weight<50
order by goods_id

SQL93网易云音乐推荐(网易校招笔试真题)

select music.music_name
from music
join (
select distinct music_name
from follow f
left join music_likes m on f.follower_id=m.user_id
left join music  s on  m.music_id=s.id
where f.user_id = 1 and music_name not in ( select music_name
                                    from music_likes m 
                                    left join music  s on  m.music_id=s.id
                                    where user_id = 1)
) t  on t.music_name=music.music_name

SQL94今天的刷题量(一)

select name,count(1) as cnt
from submission s
left join subject u on s.subject_id=u.id
where create_time=curdate()
group by name,subject_id
order by cnt desc,subject_id