牛客题霸SQL篇（不断更新中）

最新推荐文章于 2024-04-23 21:16:14 发布

badwomen__

最新推荐文章于 2024-04-23 21:16:14 发布

阅读量2.4k

点赞数

分类专栏：牛客文章标签：数据库 mysql

本文链接：https://blog.csdn.net/badwomen__/article/details/116530744

版权

牛客专栏收录该内容

1 篇文章 0 订阅

订阅专栏

牛客里面的SQL题库

之前有在自己搭建的博客里面写过，现在发现更新了不少题目，重温一遍的同时将新题目也过一遍。

题号	题目	难度	通过率
SQL1	查找最晚入职员工的所有信息	入门	42.16%
SQL2	查找入职员工时间排名倒数第三的员工所有信息	简单	38.84%
SQL3	查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no	中等	24.45%
SQL4	查找所有已经分配部门的员工的last_name和first_name	简单	40.11%
SQL5	查找所有员工的last_name和first_name以及对应部门编号dept_no	中等	39.87%
SQL7	查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t	简单	39.88%
SQL8	找出所有员工当前具体的薪水salary情况	简单	49.03%
SQL10	获取所有非manager的员工emp_no	简单	39.04%
SQL11	获取所有员工当前的manager	中等	31.64%
SQL12	获取所有部门中当前员工薪水最高的相关信息	困难	18.50%
SQL15	查找employees表所有emp_no为奇数	简单	45.14%
SQL16	统计出当前各个title类型对应的员工当前薪水对应的平均工资	中等	35.04%
SQL17	获取当前薪水第二多的员工的emp_no以及其对应的薪水salary	简单	42.48%
SQL18	查找当前薪水排名第二多的员工编号emp_no	较难	23.98%
SQL19	查找所有员工的last_name和first_name以及对应的dept_name	中等	30.43%
SQL21	查找所有员工自入职以来的薪水涨幅情况	困难	20.01%
SQL22	统计各个部门的工资记录数	中等	28.27%
SQL23	对所有员工的当前薪水按照salary进行按照1-N的排名	较难	26.04%
SQL24	获取所有非manager员工当前的薪水情况	较难	26.93%
SQL25	获取员工其当前的薪水比其manager当前薪水还高的相关信息	困难	26.00%
SQL26	汇总各个部门当前员工的title类型的分配数目	困难	23.53%
SQL28	查找描述信息中包括robot的电影对应的分类名称以及电影数目	较难	18.20%
SQL29	使用join查询方式找出没有分类的电影id以及名称	中等	37.49%
SQL30	使用子查询的方式找出属于Action分类的所有电影对应的title,description	中等	44.69%
SQL32	将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分	简单	36.12%
SQL33	创建一个actor表，包含如下列信息	中等	28.93%
SQL34	批量插入数据	简单	41.29%
SQL35	批量插入数据,如果数据已经存在，请忽略，不使用replace操作	中等	43.05%
SQL36	创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表	中等	42.93%
SQL37	对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname	中等	45.92%
SQL38	针对actor表创建视图actor_name_view	中等	43.15%
SQL39	针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005,	中等	36.37%
SQL40	在last_update后面新增加一列名字为create_date	中等	40.69%
SQL41	构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中	中等	34.67%
SQL42	删除emp_no重复的记录，只保留最小的id对应的记录。	简单	34.90%
SQL43	将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01	简单	48.53%
SQL44	将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。	简单	53.25%
SQL45	将titles_test表名修改为titles_2017	简单	57.51%
SQL46	在audit表上创建外键约束，其emp_no对应employees_test表的主键id	中等	31.34%
SQL48	将所有获取奖金的员工当前的薪水增加10%	中等	36.59%
SQL50	将employees表中的所有员工的last_name和first_name通过(’)连接起来。	中等	45.18%
SQL51	查找字符串’10,A,B’	中等	56.21%
SQL52	获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列	中等	56.17%
SQL53	按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees	中等	46.35%
SQL54	查找排除当前最大、最小salary之后的员工的平均工资avg_salary	中等	24.98%
SQL55	分页查询employees表，每5行一页，返回第2页的数据	中等	66.64%
SQL57	使用含有关键字exists查找未分配具体部门的员工的所有信息。	中等	36.21%
SQL59	获取有奖金的员工相关信息。	较难	27.23%
SQL60	统计salary的累计和running_total	较难	35.37%
SQL61	对于employees表中，给出奇数行的first_name	较难	23.56%
SQL62	出现三次以上相同积分的情况	简单	44.77%
SQL63	刷题通过的题目排名	中等	31.64%
SQL64	找到每个人的任务	简单	34.50%
SQL65	异常的邮件概率	较难	18.99%
SQL66	牛客每个人最近的登录日期(一)	简单	40.27%
SQL67	牛客每个人最近的登录日期(二)	较难	20.53%
SQL68	牛客每个人最近的登录日期(三)	较难	21.37%
SQL69	牛客每个人最近的登录日期(四)	较难	22.53%
SQL70	牛客每个人最近的登录日期(五)	困难	13.23%
SQL71	牛客每个人最近的登录日期(六)	较难	24.16%
SQL72	考试分数(一)	简单	40.13%
SQL73	考试分数(二)	中等	28.75%
SQL74	考试分数(三)	较难	18.06%
SQL75	考试分数(四)	较难	25.79%
SQL76	考试分数(五)	困难	17.96%
SQL77	牛客的课程订单分析(一)	简单	33.70%
SQL78	牛客的课程订单分析(二)	中等	27.65%
SQL79	牛客的课程订单分析(三)	中等	21.39%
SQL80	牛客的课程订单分析(四)	较难	28.29%
SQL81	牛客的课程订单分析(五)	困难	17.38%
SQL82	牛客的课程订单分析(六)	中等	17.73%
SQL83	牛客的课程订单分析(七)	较难	17.84%
SQL84	实习广场投递简历分析(一)	简单	32.40%
SQL85	实习广场投递简历分析(二)	中等	26.92%
SQL86	实习广场投递简历分析(三)	困难	15.60%
SQL87	最差是第几名(一)	中等	46.96%
SQL88	最差是第几名(二)	较难	18.06%
SQL89	获得积分最多的人(一)	中等	22.71%
SQL90	获得积分最多的人(二)	较难	19.13%
SQL91	获得积分最多的人(三)	困难	18.62%

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

在这里插入图片描述
使用子查询，注意聚合函数在分组中使用，不分组即为一个大组.

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

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

这里是引用
使用limit加子查询

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

忽略掉可能存在相同排名的情况下，可以不使用子查询

select *
from employees
order by hire_date desc
limit 2, 1
;

SQL3 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

在这里插入图片描述

简单的两个表内连接

select a.emp_no, salary, from_date, a.to_date, b.dept_no
from salaries as a
join dept_manager as b
on a.emp_no = b.emp_no 
order by a.emp_no;

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

在这里插入图片描述

同上题。

select last_name, first_name, dept_no
from dept_emp as d
join employees as e
on d.emp_no = e.emp_no;

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

在这里插入图片描述

较上题而言，需要把内连接改成左外连接

select e.last_name, e.first_name, d.dept_no
from employees as e
left join dept_emp as d
on e.emp_no = d.emp_no;

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

在这里插入图片描述

使用聚合函数count

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

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

在这里插入图片描述
分组排序

select salary
from salaries
group by salary
order by salary desc;

SQL10 获取所有非manager的员工emp_no

在这里插入图片描述

使用子查询

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

也可以使用左外连接，然后限制条件dm.emp_no is null
这里涉及到，mysql查询判断空的时候使用的是is null

select em.emp_no
from employees as em
left join dept_manager as dm
on em.emp_no = dm.emp_no
where dm.emp_no is null;

SQL11 获取所有员工当前的manager

在这里插入图片描述

在这里插入图片描述
两个表内连接

select e.emp_no as emp_no, m.emp_no as manager
from dept_emp as e
join dept_manager as m
on e.dept_no = m.dept_no
where e.emp_no <> m.emp_no
;

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

在这里插入图片描述

这个题目有所改动
先放正确答案

select d.dept_no, d.emp_no, s.salary from dept_emp d join salaries s on
d.emp_no = s.emp_no 
where s.salary = 
(
    select max(s1.salary) from salaries s1 join dept_emp d1 on
    d1.emp_no = s1.emp_no 
    where d1.dept_no = d.dept_no
)
order by d.dept_no

使用了子查询，很好理解，就是看起来比较麻烦

一般人会写

SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
GROUP BY d.dept_no
HAVING salary=MAX(s.salary);

这个答案在sqlite里面判定正确，但是在Mysql里面判定错误，MAX(SALARY) 和 emp_no 不一定对应，GROUP BY 默认取非聚合的第一条记录。
错误使用group by，select只能包含group by后的项与聚合函数。

SQL15 查找employees表所有emp_no为奇数

在这里插入图片描述
判断数字是否为奇数，可以用&1操作也可以用%2 =1

select *
from employees
where emp_no & 1
and last_name <> 'Mary'
order by hire_date desc
;

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

在这里插入图片描述

使用了聚合函数avg()

select title, avg(s.salary)
from titles as t
join salaries as s
on t.emp_no = s.emp_no
group by title
;

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

在这里插入图片描述
使用limit

select emp_no,salary
from salaries
order by salary desc 
limit 1,1;

不使用limit，通过子查询。这里涉及到的问题同12题，不可以直接用如下代码

select emp_no, max(salary)
from salaries
where salary <> (select max(salary)
              from salaries);

正确写法为

select emp_no, max(salary)
from salaries
where salary = 
                (select max(salary)
                 from salaries 
                 where salary <> (select max(salary)
                 from salaries));

SQL18 查找当前薪水排名第二多的员工编号emp_no

在这里插入图片描述

同上题，涉及到聚合函数，用到了子查询。在此题中，嵌套的子查询比较多

select e.emp_no, s.salary, e.last_name, e.first_name
from employees as e, salaries as s
where e.emp_no = s.emp_no
and s.to_date = '9999-01-01'
and s.salary = (
    select max(salary)
    from salaries
    where salary < (
        select max(salary)
        from salaries
    )
)

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

在这里插入图片描述

因为要查没有分配部门的员工，三个表，需要两次左外连接

select last_name, first_name, dept_name
from employees as e 
left join dept_emp as dm
on e.emp_no = dm.emp_no
left join departments as d
on d.dept_no = dm.dept_no;

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

在这里插入图片描述

在这里插入图片描述
这题看起来还是有难度的。虽然只有三张表，但是为了获得涨幅，就必须重复利用薪水表。

select e.emp_no, (s1.salary - s2.salary) as growth
from employees as e
join salaries as s1
on e.emp_no = s1.emp_no
and s1.to_date = '9999-01-01'
join salaries as s2
on e.emp_no = s2.emp_no
and e.hire_date = s2.from_date
order by growth
;

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

在这里插入图片描述

这里表中的sum为误导项，实际上是count函数的利用

select d.dept_no, d.dept_name, count(*) as sum
from departments as d
join dept_emp as de
on d.dept_no = de.dept_no
join salaries as s
on de.emp_no = s.emp_no
group by d.dept_no
order by d.dept_no;

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

在这里插入图片描述
主要考察窗口函数中用于排序的专用窗口函数用法

SELECT emp_no, salary, DENSE_RANK() OVER(ORDER BY salary DESC) t_rank
FROM salaries

下面介绍三种用于进行排序的专用窗口函数：

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略

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

在这里插入图片描述

故意多个了一个表，实际上很简单

select de.dept_no, de.emp_no, salary
from dept_emp as de, dept_manager as dm, salaries as s
where de.dept_no = dm.dept_no
and de.emp_no = s.emp_no
and de.emp_no <> dm.emp_no
;

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

在这里插入图片描述

和21题相似，用了两次薪水表

select de.emp_no, dm.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary
from dept_emp as de, dept_manager as dm, salaries as s1, salaries as s2 
where de.dept_no = dm.dept_no
and de.emp_no = s1.emp_no
and dm.emp_no = s2.emp_no
and s1.salary > s2.salary
;

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

在这里插入图片描述

group by可以连续使用

select de.dept_no, dept_name, title, count(*) as count
from departments as d, dept_emp as de, titles as t
where d.dept_no = de.dept_no
and de.emp_no = t.emp_no
group by de.dept_no, title
order by de.dept_no
;

SQL28 查找描述信息中包括robot的电影对应的分类名称以及电影数目

在这里插入图片描述

用到了like和子查询，这里使用in和=都可以

select c.name, count(f.film_id)
from film as f, film_category as fc, category as c
where f.film_id = fc.film_id
and c.category_id = fc.category_id
and f.description like '%robot%' 
and c.category_id in (select category_id
                     from film_category
                     group by category_id
                     having count(film_id) >= 5)
group by c.name
;

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

在这里插入图片描述

和前面第十题题目很像，使用left join加上判断空

select f.film_id, f.title
from film as f
left join film_category as fc
on f.film_id = fc.film_id
left join category as c
on fc.category_id = c.category_id
where c.name is null;

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

在这里插入图片描述

子查询的应用

select title, description
from film as f
left join film_category as fc
on f.film_id = fc.film_id
where fc.category_id = (select category_id
                    from category
                    where name = 'Action');

SQL32 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

在这里插入图片描述

Mysql中concat() 字符串拼接函数的使用

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

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

在这里插入图片描述

create table if not exists actor
(
    actor_id smallint(5) primary key,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update	date not null 
);

SQL34 批量插入数据

在这里插入图片描述

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

SQL36 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

在这里插入图片描述

create table actor_name as
select first_name,last_name from actor;

SQL37 对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);

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，查询emp_no为10005,

在这里插入图片描述
MYSQL中强制索引查询使用：FORCE INDEX(indexname);

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

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

在这里插入图片描述

alter table actor
add create_date datetime not null default('2020-10-01 00:00:00');

SQL41 构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中

在这里插入图片描述
构造触发器时注意以下几点：
1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间

create trigger audit_log
after insert on employees_test
begin
insert into audit values(New.ID, NEW.name);
end;

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

在这里插入图片描述
先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM
… WHERE … NOT IN … 语句删除 “非每个分组最小id对应的所有记录”

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

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 to_date = '9999-01-01';

SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

在这里插入图片描述
replace用法replace(字段/字符串，“需要替换的值”，“替换后的值”）

update titles_test
set emp_no = replace(emp_no, 10001, 10005)
where id = 5
and emp_no = 10001;

SQL45 将titles_test表名修改为titles_2017

在这里插入图片描述

alter table titles_test rename to titles_2017;

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

在这里插入图片描述

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

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

在这里插入图片描述

UPDATE salaries 
SET salary = salary * 1.1 
WHERE emp_no IN (
    SELECT emp_no FROM emp_bonus)
and to_date='9999-01-01';

SQL50 将employees表中的所有员工的last_name和first_name通过(')连接起来

在这里插入图片描述
同32题，使用concat

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

SQL51 查找字符串'10,A,B'

在这里插入图片描述
把串 “10,A,B” 中的逗号用空字符串替代，变成了 “10AB”
然后原来串的长度 - 替换之后的串的长度就是被替换的逗号的个数

select length('10,A,B') - length(replace('10,A,B',",",""));

SQL52 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

在这里插入图片描述
substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

select first_name from employees
order by substr(first_name,length(first_name)-1,2)

或

select first_name from employees
order by substr(first_name,-2,2)

SQL53 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

在这里插入图片描述
group_concat(X)，group_concat(X,Y)。group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y，将会在每个X之间用Y作为分隔符。如果省略了Y，“，”将作为默认的分隔符。每个元素连接的顺序是随机的。

select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no;

SQL54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

在这里插入图片描述
题目有点问题，应该改为“查找当前(to_date = ‘9999-01-01’ )排除最大、最小salary之后员工的平均工资avg_salary。”

select avg(salary) as avg_salary
from salaries
where  to_date = '9999-01-01'
and salary <> (select max(salary) from salaries where to_date = '9999-01-01')
and salary <> (select min(salary) from salaries where to_date = '9999-01-01')
;

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

在这里插入图片描述
limit的使用

select *
from employees
limit 5, 5;

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

在这里插入图片描述
什么时候用EXISTS，什么时候用IN？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

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

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

在这里插入图片描述
case语法：
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

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

或

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

SQL60 统计salary的累计和running_total

在这里插入图片描述
使用窗口函数

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

使用子查询替代窗口函数

SELECT s2.emp_no,s2.salary,SUM(s1.salary) AS running_total
FROM salaries AS s1 INNER JOIN salaries AS s2 
ON s1.emp_no <= s2.emp_no
WHERE 
s1.to_date = "9999-01-01"
AND s2.to_date = "9999-01-01"
GROUP BY s2.emp_no

从这题的两种答案可以看出，窗口函数可以用子查询来替代

SQL61 对于employees表中，给出奇数行的first_name

在这里插入图片描述

使用窗口函数

select t1.first_name
from employees t1 join
(
    select first_name,row_number() over(order by first_name) as rank_number
    from employees
) t2
on t1.first_name=t2.first_name
where t2.rank_number %2 !=0

同上题，可以使用窗口函数也可以使用子查询
使用子查询

select e1.first_name from employees e1
where
(select count(*) from employees e2 where
e1.first_name>=e2.first_name
)%2 = 1

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

在这里插入图片描述

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

SQL63 刷题通过的题目排名

在这里插入图片描述

同题60

使用窗口函数

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

使用子查询

select a.id, a.number, count(distinct b.number) as t_rank
from passing_number as a
inner join passing_number as b
on a.number <= b.number
group by a.id, a.number
order by t_rank asc

SQL64 找到每个人的任务

在这里插入图片描述

select p.id, name, content
from person as p
left join task as t
on p.id = t.person_id
order by p.id;

SQL65 异常的邮件概率

在这里插入图片描述

在group by分组内使用case函数之后，再使用聚合函数

select date, round(
    sum(case email.type when 'completed' then 0 else 1 end) * 1.0 / count(email.type), 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 
order by date;

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

在这里插入图片描述

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

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

在这里插入图片描述

经典错误解答

select u.name as u_n, c.name as c_n, max(date) as date
from login as l
join user as u
on l.user_id = u.id
join client as c
on l.client_id = c.id
group by u_n
order by u_n
;

同题12，因为错误使用group by，select只能包含group by后的项与聚合函数，不然匹配顺序就不对了。

正确答案为

Select u.name as u_n, c.name as c_n, l.date as date
From login l inner join user u
ON l.user_id = u.id
inner join client c
ON l.client_id = c.id
Where (l.user_id, l.date) in 
(Select l1.user_id, max(l1.date) 
 From login l1 Group by l1.user_id)
Order by u.name asc
;

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

在这里插入图片描述

套子查询，利用题目给的函数

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

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

在这里插入图片描述

利用窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可。
注意mysql中rank为关键字

select a.date,
sum(case when t_rank=1 then 1 else 0 end) as new
from (select date,row_number()over(partition by user_id order by date)as t_rank
     from login) as a
group by a.date
order by a.date
;

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

在这里插入图片描述

综合前面的题目，比较麻烦


select 
date,
case when new_uid=0 then 0.000  
    else round(retained_uid *1.0/new_uid,3) end as p

from 
(
    select t1.date, 
    sum(case when t2.user_id is null then 0 else 1 end) as retained_uid,
    sum(case when ranking=1 then 1 else 0 end) as new_uid
from 
(select user_id, 
        date, 
        rank() over(partition by user_id order by date) as ranking
from login) as t1
left join  login as t2
on t1.user_id = t2.user_id 
    and date_add(t1.date, interval 1 day)=t2.date
group by t1.date
order by t1.date
) as temp

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

在这里插入图片描述

本题不需要使用到第一个login表，采用sum函数进行开窗（窗口函数）处理，将user_id进行分区，再通过时间升序排序，进而实现了在每个user_id分区中以升序日期排序的通过题数的逐个递加（在这一步中，user_id的排序不重要，date的排序才重要），即“截止到某天，累计总共通过了多少题”的题意。

SELECT name AS u_n,date,sum(number) over(partition by user_id order by date) AS ps_num
FROM passing_number,user
WHERE passing_number.user_id=user.id
GROUP BY date,u_n
ORDER BY date,u_n

SQL72 考试分数(一)

在这里插入图片描述

查询各个岗位： GROUP BY job
分数的平均数据：AVG（score）
降序：ORDER BY xx DESC
保留三位小数：round(AVG（score）,3)

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

SQL73 考试分数(二)

在这里插入图片描述

子查询，一个表用两次

SELECT g1.*
FROM grade g1
WHERE score > (SELECT AVG(score)
              FROM grade g2
              WHERE g2.job = g1.job
              GROUP BY job)
ORDER BY g1.id;

SQL74 考试分数(三)

在这里插入图片描述

使用窗口函数

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

使用子查询

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 
    where g2.score>=g1.score and g1.language_id=g2.language_id
) <=2 order by l.name asc,g1.score desc ,g1.id asc;

同60题

SQL75 考试分数(四)

在这里插入图片描述

中位数的特征：
当个数为偶数时，中位数的起始位置等于个数/2，结束位置等于个数/2+1
当个数为奇数时，中位数的起始位置等于向上取整（个数/2），结束位置等于向上取整（个数/2）
用除以2的余数是否为0来判断奇偶，%2=0
记得取整数，本题用ceiling函数向上取整（返回不小于该数的最小整数值）或round(数，0)四舍五入取整都可。

select 
    job,
    floor((count(job)+1)/2) as start,
    ceiling((count(job)+1)/2) as end
from grade
group by job
order by job
;

SQL76 考试分数(五)

在这里插入图片描述

无论奇偶，中位数的位置距离（个数+1）/2 小于1
思路巧妙

select id,job,score,r from
(select *,rank()over(partition by job order by score desc)as r,count(*)over(partition by job)as t
from grade) as A
where round(abs(r-(t+1)/2)*1.0,2)<1
order by id

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

在这里插入图片描述

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

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

在这里插入图片描述

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(*) > 1
order by user_id
;

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

在这里插入图片描述

select o.* 
from order_info as o
where 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(*) > 1)
and date > '2025-10-15'
and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
order by id
;

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

在这里插入图片描述

select user_id, min(date) as dirst_buy_date, count(*) as cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
group by user_id
having count(*)>1
order by user_id
;

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

在这里插入图片描述

用了case语句和窗口函数，有点复杂

select
 a.user_id,
 max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
 max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date >= '2025-10-16'
      and status = 'completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

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

在这里插入图片描述

逻辑是买了两次的然后看是否拼团

select o.id, o.is_group_buy ,
case when o.is_group_buy = 'No'then c.name else NULL end
from order_info as o left join client as c
on o.client_id = c.id
where 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(id) > 1)
and o.date > '2025-10-15'
and o.product_name in ('C++','Java','Python')
and o.status = 'completed'
order by o.id asc

除此之外，发现client_id为的就是拼团
可以使用窗口函数来计算购买完成两次以上的，使用左连接来获取client名字
特此注意在窗口函数中不要加order by ，如果向下面一样，就会错误：

count(1) over (partition by user_id order by date) cnt

原因是窗口函数中 order by 不仅仅是排序的功能，实质上是累加的功能。
具体分析参考此博客：https://blog.csdn.net/dhr223/article/details/107413344

select T.id, T.is_group_buy, T2.name
from (
    select id, is_group_buy, client_id, count(1) over (partition by user_id) cnt
    from order_info
    where date > '2025-10-15'
    and product_name in ('C++', 'Python', 'Java')
    and status = 'completed'
) T
left join client T2 on T2.id = T.client_id
where cnt > 1
order by T.id

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

在这里插入图片描述

和上题比较类似

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

也可以利用窗口函数

select (case when client_id=0 then 'GroupBuy' else 
        (select name from client c where c.id = nt.client_id ) end) as source,
        count(*) from(select *, count(id) over(partition by user_id) as cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('Java', 'Python', 'C++')) nt
where cnt >= 2
group by source
order by source

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

在这里插入图片描述

使用left函数或者like或者year(date)

select job, sum(num) as cnt
from resume_info
where left(date, 4) = '2025'
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) as cnt
from resume_info
where year(date) = '2025'
group by job
order by cnt desc
;

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

在这里插入图片描述

left+用两个列group by

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

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

在这里插入图片描述

主要还是字符串的处理

select first_year.job,first_year.mon as first_year_mon,first_year.cnt as first_year_cnt,second_year.mon as second_year_mon,second_year.cnt as second_year_cnt
from
(select job,substr(date, 1, 7) as mon,sum(num) as cnt 
 from resume_info 
 where left(date, 4) = '2025'
 group by job,mon) as first_year
 
join
 
(select job,substr(date, 1, 7) as mon,sum(num) as cnt 
 from resume_info 
 where left(date, 4) = '2026' 
 group by job,mon) as second_year
 
on first_year.job=second_year.job
and substr(first_year.mon, length(first_year.mon) - 1,2)=right(second_year.mon,2)
order by first_year.mon desc, first_year.job desc
;

SQL87 最差是第几名(一)

在这里插入图片描述

窗口函数sum(number) over(order by grade)

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

SQL88 最差是第几名(二)

在这里插入图片描述

当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

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
    ) as t
where a >= total/2 and b >= total/2  -- 正序逆序均大于整个数列数字个数的一半
order by grade;

需要注意的是去掉t不可以，因为每个派生出来的表都必须有一个自己的别名

也可以写成

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

如果写成

select grade, total, total1
from
    (select grade,
     sum(number) as total1,
    sum(number)over(order by grade) a, 
    sum(number)over(order by grade desc) b 
     from class_grade
    ) as t

order by grade;

会打断循环，因为开窗函数和聚合函数的不同之处是：开窗函数对于每个组返回多行，而聚合函数对于每个组只返回一行

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

在这里插入图片描述

select name, g2.grade_sum
from user as u
join (select user_id, sum(grade_num) as grade_sum
from grade_info as g1
group by user_id) as g2
on u.id = g2.user_id
order by g2.grade_sum desc
limit 1
;

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

在这里插入图片描述

子查询

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

使用窗口函数

select t1.id,t1.name,t0.grade as grade_sum
from(
    select user_id,grade,rank()over(order by grade desc) as t
    from(
        select user_id,sum(grade_num) as grade
        from grade_info
        group by user_id
        ) a0
) t0
join user as t1
on t0.user_id = t1.id
where t=1

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

在这里插入图片描述

较之上一题增加了case语句

select t1.id,t1.name,t0.grade as grade_sum
from(
    select user_id,grade,rank()over(order by grade desc) as t
    from(
        select user_id,sum(case when type='add' then grade_num else -grade_num end) as grade
        from grade_info
        group by user_id
        ) a0
) t0
join user as t1
on t0.user_id = t1.id
where t=1

题目来自于牛客网https://www.nowcoder.com/ta/sql
部分题解借鉴于网友

badwomen__

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
牛客题霸SQL篇（不断更新中）

牛客里面的SQL题库 之前有在自己搭建的博客里面写过，现在发现更新了不少题目，重温一遍的同时将新题目也过一遍。 题号题目难度通过率SQL1查找最晚入职员工的所有信息入门42.16%SQL2查找入职员工时间排名倒数第三的员工所有信息简单38.84%SQL3查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no中等24.45%SQL4查找所有已经分配部门的员工的last_name和first_name简单40
复制链接

扫一扫

专栏目录