牛客MySQL：错题

总结前两天摸出来的牛客MySQL专栏的错题，后面回顾的时候再进行细分吧。。。

不用order by进行找第二高的工资的员工情况

题目描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

解题思路

题目要求的是要薪水第二高的员工信息，而且不可以使用order by去完成

也就是不可以排序，那么只能去找到第二高的薪水是什么了，然后使用where来找出对应数据

使用下面语句可以找出salary最大值

SELECT MAX(salary) FROM salaries;

那么只要使用相关子查询，将最大的排除掉，然后再聚合一次找最大的，就可以得到第二大

使用下面的SQL我们就可以找到第二高的salary是多少了

SELECT MAX(s.salary) FROM salaries s 
where s.salary < (SELECT MAX(s2.salary) FROM salaries s2); 

然后再使用where去过滤即可

SELECT .... FROM salaries s3 
where s3.salary = (SELECT MAX(s.salary) FROM salaries s
                  WHERE s.salaary < (SELECT MAX(s2.salary) FROM salaries s2)); 

当然，这一题目的要求，还要去联结员工表，简单使用内联接即可。

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

题目描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01’时，表示依然在职，无后续调整记录）

解题思路

salaries表记录了所有的员工的工资变更情况，而且有from_data和to_data记录了这段工资维持的时间，那么我们就可以通过对salaries自身内联接得出每个员工每段时间的变化（让to_data = from_data）

根据下面的SQL就可以让每一段时间的工资变化合在一起

select (s2.salary-s1.salary) as growth 
from salaries s1,salaries s2 
where s1.emp_no = s2.emp_no and s1.from_date = s2.to_date;

举个栗子，假如一个员工有两条记录

他2021/5/28入职，工资为100，然后第二天就升为了200，那么数据库表的记录就会这样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nUt1186X-1622560205601)(C:\Users\111\AppData\Roaming\Typora\typora-user-images\image-20210528194019203.png)]

那么5/29就是一个涨值期，对应的涨值为100~200（只要to_data不为9999/01/01，一定会有一个变动记录的from_date等于它，因为发生了变动）

那么只需要使用自身内联接，让s2.from_date = s1.to_date，就可以得出所有的变值过程了。

变动的值就是s2.salary - s1.salary，也就是growth

得到所有的变动值，接下来就简单了

只要将所有的变动值统计起来，就可以得到涨值了，所以还要进行聚合，由于题目需求，我们对emp_no进行group by分块

select sum(s2.salary-s1.salary) as growth from salaries s1,salaries s2 
where s1.emp_no = s2.emp_no  
and s1.to_date = s2.from_date group by s1.emp 
group by s1.emp_no 

由于题目需要的是在职的，所以我们再用having将不在职的分组给去掉（此时一个分组就代表一个员工了），过滤的方法就是看s2的to_data最大值是否是9999-01-01，因为我们联结方式是s1.to_data = s2.from_data，代表s2的数据是改动后的数据，所以要改动后的日期为未离职时间

select sum(s2.salary-s1.salary) as growth from salaries s1,salaries s2 
where s1.emp_no = s2.emp_no  
and s1.to_date = s2.from_date group by s1.emp 
group by s1.emp_no 
having max(s2.to_date) = '9999-01-01';

最后再进行一个排序

select s1.emp_no,sum(s2.salary - s1.salary) as growth from salaries s1,salaries s2 
where s1.emp_no = s2.emp_no  
and s1.to_date = s2.from_date 
group by s1.emp_no 
having max(s2.to_date) = '9999-01-01'
order by growth

总结

当一个表记录着某一属性的变动，可以使用自身联结的方式找到每一段的变动（前提是该表有两个属性记录着某行

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

题目描述

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列：

题解思路

排序使用一个order by salary即可，但问题出在要如何统计t_rank。

select s.emp_no,s.salary from salaries s order by s.salary desc;

我想到的是使用关联子查询，统计salaries表里面有多少个salary是大于当前salary的，然后进行加1即可，当然还要进行相同salary去重，这里也可以改成大于等于，那样就不需要进行加1

select s.emp_no,s.salary,
(select count(distinct s2.salary) from salaries s2 where s2.salary >= s.salary)
from salaries s order by s.salary desc;

查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类包含电影总数量>=5部

题目描述

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

​ CREATE TABLE IF NOT EXISTS film (

​ film_id smallint(5) NOT NULL DEFAULT ‘0’,

​ title varchar(255) NOT NULL,

​ description text,

​ PRIMARY KEY (film_id));

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

​ CREATE TABLE category (

​ category_id tinyint(3) NOT NULL ,

​ name varchar(25) NOT NULL, last_update timestamp,

​ PRIMARY KEY ( category_id ));

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

​ CREATE TABLE film_category (

​ film_id smallint(5) NOT NULL,

​ category_id tinyint(3) NOT NULL, last_update timestamp);

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部 查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类包含电影总数量>=5部

解题思路

除了最后的判断该分类包含电影总数量>=5部，前面的都是很简单

使用内联接将三张表都联结起来，然后使用where来对robot进行模糊查询，再对电影名字进行group by即可

写出的SQL如下

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%' 
group by c.name;

那么要怎么判断该分类的电影数目要大于5呢

我这里的解决办法就是使用子查询，在having后对当前组的category_id进行子查询来查该分类有多少条电影数目

由于要在having使用关联子查询，所以group by 必须要拥有该字段才可以关联起来，需要加上c.category_id

最后的SQL修改成下面这样

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%' 
group by c.name,c.category_id 
having 5 <= (select count(fc2.film_id) from film_category fc2 
           where fc2.category_id = c.category_id);

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

题目描述

题目已经先执行了如下语句:

drop table if exists actor; CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL); insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

actor_id	first_name	last_name	last_update
‘3’	‘ED’	‘CHASE’	‘2006-02-15 12:34:33’

解题思路

第一次知道MySQL有ignore的写法，忽略相同的数据

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

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

题目描述

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

CREATE TABLE actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  datetime NOT NULL);

解题思路

MySQL添加4种索引的方式

//添加主键，不能为null且必须唯一
alter table tbl_name add primary key(索引列);
//添加一个唯一索引
alter table tbl_name add unique 索引名字(索引列);
//添加一个普通索引
alter table tbl_name add index 索引名字(索引列);
//添加一个全文索引
alter table tbl_name add fulltext 索引名字(索引列);

配上删除索引的方式

//删除非聚簇索引
drop index 索引名字 on tbl_name;
alter table tbl_name drop index 索引名字;
//删除主键索引
alter table tbl_name drop primary key;

针对actor表创建视图actor_name_view

题目描述

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

CREATE TABLE  actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update datetime NOT NULL);

解题思路

创建视图的语句为

create view view_name as SQL;

解题SQL

create view actor_name_view 
as 
select first_name as first_name_V,last_name as last_name_v from actor;

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

题目描述

存在actor表，包含如下列信息：

CREATE TABLE  actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  datetime NOT NULL);

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

解题思路

增加列的SQL为

alter table tbl_name add (列信息);

解题SQL

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

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引

题目描述

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
create index idx_emp_no on salaries(emp_no);

解题思路

使用强制索引

select .. from  tbl_name force index(索引名字) where ..

解题SQL

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

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

题目描述

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

 CREATE TABLE IF NOT EXISTS titles_test (
 id int(11) not null primary key,
 emp_no  int(11) NOT NULL,
 title  varchar(50) NOT NULL,
 from_date  date NOT NULL,
 to_date  date DEFAULT NULL);

解题思路

首先第一步，我们要找到哪些要保留的id，注意到这里的id是作为primary key，所以一定不可能出现重复的，也就是不可能出现有emp_no对应的最小id出现在其他emp_no里面

下面SQL就可以获取到需要保留的最小id

select min(t.id)
from titles_test t 
group by t.emp_no;

接下来，就可以进行删除

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

然后就会发现报错

提示不可以边读边删（这是因为update操作会拿排他锁，与共享锁发生冲突！）

既然不可以对一张表进行边读边删，那么可以让读的表是一张临时表

delete from titles_test 
where id not in(
	select * from (
    	select min(t.id)
        from titles_test t
        group by t.emp_no
    )as temp //注意要给子查询表加别名
)

使用replace进行删除

题目描述

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

解题思路

第一次知道replace。。。

使用replace可以减少一次where判断

语法为：

replace(字段名,被替代值,替代值)

比如上面的需求使用update的实现为

update t set emp_no = 10001 where emp_no = 10005 and id = 5;

如果改成replace就可以省略掉emp_no = 10005，只要将id=5的行数据的10005都变成10001即可

update t set emp_no = relace(emp_no,10005,10001) where emp_no = 5;

修改表的名字

题目描述

将titles_test表名修改为titles_2017

解题思路

修改表的名字仍然使用alter table ，然后使用rename to来修改名字

alter table tbl_name rename to new_name;

解题SQL

alter table tbl_name rename to titles_2017;

增加外键

题目描述

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

解题思路

增加表外键的SQL

alter table tbl_name add constraint foreign key(column1)
references tbl_name_two(column2)

解题SQL

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

查找指定字符串中逗号,出现的次数cnt

题目描述

查找字符串 10,A,B 中逗号,出现的次数cnt

解题思路

让原本字符串的长度减去去掉逗号后字符串的长度即可

length(str) //获取原字符串长度
replace(str,",","") //使用replace去掉字符串里面的逗号

解题SQL

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

只根据字段的左右两边指定字符order by

题目描述

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

解题思路

使用right和left来指定order by的比较方法

//指定只比较后面右边两个字符，也就是最后面2个
select first_name from employees order by right(first_name,2);

组concat

题目描述

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

解题思路

使用group_concat来进行组的拼接，group_concat默认的就是使用逗号来连接

select group_concat(emp_no)
from dept_no
group by emp_no

使用EXISTS判断是否存在

题目描述

使用含有关键字exists查找未分配具体部门的员工的所有信息。共有两张表，一张employees，另一张为dept_emp，使用emp_no关联在了一起

解题思路

这道题其实使用一个外联接就可以解决，但只允许使用exists

使用exists一般都是伴随着子查询去做的

解题SQL

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

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

题目描述

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

共有4张表，一张员工表employees，一张部门表dept_emp，另一张奖金表emp_bonus还有一张为薪资表salaries（四张表通过emp_no关联起来）

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’

解题思路

首先将这4张表内联起来，内联薪资表时，记得还要匹配当前薪水的(to_date = ‘9999-01-01’)

重点在于如何对奖金进行判断（bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%）

使用case when then end语句来判断

case
when ...
then ...
end 

具体形式为

(case when emp_bonus.btype = 1 then salaries * 0.1
when emp_bonus.btype = 2 then salaries * 0.2
else salaries * 0.3) as bonus

加上之前的内联结，解题SQL为

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

统计salary的累计和running_total

题目描述

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。

 CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)); 

输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

解题思路

第一种，对running_total使用子查询，去叠加上面的数据

select s.emp_no,s.salary,
(select sum(s2.salary) from salaries s2 
 	where s2.emp_no <= s.emp_no and s2.to_date = "9999-01-01") 
 	as running_total
from salaries s
where to_date = "9999-01-01";

第二种，使用滑动窗口函数

首先我们来认识一下窗口函数

窗口函数是group by的改进，数据进行了group by之后，会变成一块块，返回的数据相当于就只有一行了，而窗口函数不会合并成一行，会每行都返回出来。

语法如下

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

举个栗子

我这里有一张成绩表

现在对class进行聚合查每个班级的总score

SELECT id,SUM(score) FROM t13 GROUP BY class;

可以看到，这里都变成了一条行数据，不是id列没有使用聚合函数进行处理返回了每一块的第一行

现在我们使滑动窗口函数进行聚合

SELECT id,SUM(score) over (PARTITION BY class) FROM t13; 

并没有像group by一样杂糅成一行

如果我们不进行分区会怎么样？

SELECT id,SUM(score) over () FROM t13; 

可以看到，如果不进行分区，就默认整个是一个分区

假如我们进行排序而不进行分区又会怎样

SELECT id,SUM(score) over (ORDER BY id) FROM t13; 

可以看到，分区的规则是前N个（N代表第几行，比如第一行，那么对应的分区就是前一行，第二行时，对应的分区就是前两行，。。。）

那么我们使用窗口函数的这个性质，就可以得出前N个总和了

题解SQL

SELECT emp_no, salary, 
SUM(salary) OVER(ORDER BY emp_no) AS running_total 
FROM salaries 
WHERE to_date = '9999-01-01';

拓展

下面拓展一些分区专用的函数

1. rank() ：并列排名，继续累加
2. dense_rank：并列排名，不继续累加
3. row_number：不并列排名

举个栗子

我们往原来的表加几条数据，现在表变成了这样

然后我们看看这几个函数的区别

SELECT rank() over (ORDER BY score DESC),
dense_rank() over (ORDER BY score DESC),
row_number() over(ORDER BY score DESC)
FROM t13; 

可以看到rank，是出现重复排名的，而且7~9还断开了，这是因为出现了3个7，而dense_rank也会出现重复排名，不过并不会累计，最后一名还是8，而row_number则是不并列排名，单纯记录第几行

注意，这里的order by score desc使得窗口函数的分区规则依然是排序后的前N行

取出奇数排名的行数据

题目描述

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

 CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,

  PRIMARY KEY (`emp_no`)); 

如，输入为：

  INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
 INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
 INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
 INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

输出格式:

first_name
Georgi
Anneke

因为Georgi按first_name排名为3，Anneke按first_name排名为1，所以会输出这2个，且输出时不需排序。

解题思路

刚开始的时候，打算肯定是找出奇数排名的顺序，所以设置了下面的SQL

SELECT row_number() over(order by es.first_name) as `rank`
FROM employees es where `rank` % 2 != 0;

却发现会报错，显示找不到rank列

为什么会找不到rank列？SQL的执行顺序不应该是加载完所有列，最后再进行where的过滤的嘛？

但后面又想了想，会不会是MRR或者ICP优化SQL，然后WHERE过滤放在了存储引擎层，导致在WHER过滤时还没有加载列？

最后解决的办法就是生成一张排名的临时表，然后外面一层再利用临时表的排名进行WHERE过滤

SELECT temp.rank 
FROM (SELECT row_number() over(order by es.first_name) as `rank`
     FROM employees es) as temp
     WHERE temp.rank % 2 != 0;

这样是可以行的

那问题就好解决了，我们需要的是first_name列，而且使用了子查询生成临时表，所以我们要在临时排名表上加上first_name列。

SELECT temp.first_name 
FROM (SELECT first_name,row_number() over(order by es.first_name) as `rank`
     FROM employees es) as temp
     WHERE temp.rank % 2 != 0;

最后一个问题，这样的SQL对first_name的输出是按排名进行输出的，但题目的要求则是不按排名进行输出，所以我们外层还要再嵌套一层，使用IN去让排名变回原来顺序

SELECT first_name FROM employees es2 
WHERE es2.first_name IN (SELECT temp.first_name
                        FROM (SELECT first_name,row_number() over(order by es.first_name)
                             as `rank`
                             FROM employees es) as temp
                        WHERE temp.rank %2 != 0);

异常的邮件概率

题目描述

描述

现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
…
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
…
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;

下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
。。。
第4行表示id为4的是正常用户

现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

结果表示:

2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500;

2020-01-12没有失败的情况，所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

解题思路

需求要的是正常用户发生给正常用户的失败概率

所以，第一步要找到正常用户发送给正常用户的所有记录

这一步实现很简单，只需要使用两次内联接(要各自匹配)，一次过滤非正常用户的发送方，另一次过滤非正常用户的接收方

select *
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 != 1 and u2.is_blacklist != 1;

这样就得到了想要的数据

接下来就是进行分组，然后计算即可，计算使用case来将completed转为0，no_completed转为1，然后进行叠加即可

select e.date,(sum(case e.type when completed then 0
                  when no_completed then 1)/count(e.type)) as 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 != 1 and u2.is_blacklist != 1
group by e.date;

但还有一个问题未解决，只保留3为小数，所以还要使用到round函数

select e.date round(sum(case e.type when completed then 0
                       when no_completed then 1)/count(e.type),3) as 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 != 1 and u2.is_blacklist != 1
group by e.date;

考试分数(三)

题目描述

牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rvxz71M6-1622560376216)(C:\Users\111\AppData\Roaming\Typora\typora-user-images\image-20210529231445754.png)]
第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
…
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，

不同的语言岗位(language)表简化如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gOm7b7VD-1622560376219)(C:\Users\111\AppData\Roaming\Typora\typora-user-images\image-20210529231415413.png)]
请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M1YYZsHZ-1622560376220)(C:\Users\111\AppData\Roaming\Typora\typora-user-images\image-20210529231406215.png)]

解题思路

首先题目需要的是每个岗位前两名，前两名代表了不止一条，所以不能使用group by，使用窗口函数，又没有合适的聚合函数，所以最外层应该要套用子查询

首先我们使用相关子查询去对应找到每个岗位的前两名

select 
from grade g
inner join `language`l on g.language_id = l.id
where g.id in (
	select g2.id
    from grade g2 
    inner join `language` l2 on g2.language_id = l2.id
    where l2.id = l.id //关联子查询) as temp
)  order by l.name,g.score desc,g.id;

但我们发现，因为使用了IN，所以限制了子查询里面的只能由id列，但我们还需要进行聚集排名呀，所以还需要一层子查询

select g.id,l.name,g.score
from grade g
inner join `language` l on g.language_id = l.id
where g.id in (
    //加多一层封装，只要取id列
    select temp.id from (
        //下面这层SQL用来聚集排名
               select (dense_rank() over (order by g2.score desc)) as paiming ,g2.id
              from grade g2 inner join `language` l2 
               on g2.language_id = l2.id 
              where l2.id = l.id) as temp 
where temp.paiming <= 2 //让排名前2的，还要包括并列排名，所以选择用dense_rank
             order by l.name,g.score desc,g.id;

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

题目描述

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-26的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。

​

​

请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，以上例子查询结果如下:

解析:

id为4，6的订单满足以上条件，输出57，id为4的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为6的订单为第二次购买，输出second_buy_date为2025-10-24，总共成功购买了2次;

id为5，7，8的订单满足以上条件，输出557336，id为5的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为7的订单为第二次购买，输出second_buy_date为2025-10-25，总共成功购买了3次;

解题思路

前面的4道题都是使用相关子查询对应找到符合情况的user_id，但因为这道题还需要找到第二次购买日期，也就是不单要在外层进行聚合，子查询也要进行聚合，所以子查询要查找的是符合条件的user_id，而且还要有对应的cnt和排名，这就需要使用到窗口函数了

select oi.user_id,
dense_rank() over(partition by oi.user_id order by oi.date) as paiming,
count(*) over(partition on by oi.user_id) as cnt 
from order_info oi
where oi.date > "2025-10-15"
and oi.status = "completed"
and 
(oi.product_name = "Java" and oi.product_name = "Python" and oi.product_name = "C++");

上面的SQL我们就可以找到每个user_id，所在组的对应的购买产品日期的排名，和所在组的购买产品的数量

接下来再嵌套一个子查询，就可以进行筛选出数量大于2的user_id，且只要前两个日期排名的数据（注意这里是对应每个组的，也就是每个组的前两个日期排名的数据）

然后进行对user_id进行group by聚合，然后取每组的日期的最大和最小分别对应第二日期和第一日期（由于还需要日期，所以在子查询里面还需要加上日期数据）

select temp.user_id,
min(temp.date) as first_buy_date,
max(temp.date) as second_buy_date,
temp.cnt 
from (
	select oi.user_id,
	dense_rank() over(partition by oi.user_id order by oi.date) as paiming,
	count(*) over(partition on by oi.user_id) as cnt,
    oi.date 
	from order_info oi
	where oi.date > "2025-10-15"
	and oi.status = "completed"
	and 
	(oi.product_name = "Java" and oi.product_name = "Python" and oi.product_name = "C++")
) as temp 
where temp.paiming <= 2
and temp.cnt >= 2
group by temp.user_id;

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

题目描述

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的**非拼团****(is_group_buy为No)**订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的非拼团**(is_group_buy为No)**订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团**(is_group_buy为Yes)订单，拼团不统计客户端，所以client_id所以为0**，状态为购买成功。

​

有一个客户端表(client)，简况如下:

​

请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序，以上例子查询结果如下:

解题思路

这道题的思路与前面课程订单分析5十分类似，只不过这里还要加一个小步骤就是将is_group_buy为yes时，对应的设备名字改为GroupBuy

使用case语法即可

case语法建议使用case when 判断 then xxx else xx end 这种格式去对应修改，而不是使用case xx when。。

select temp.source,count(temp.source) as cnt
from 
(select oi.user_id,
 count(*) over(partition by oi.user_id) as cnt2,
 (case when oi.is_group_buy = "Yes" then "GroupBuy"
 else cl.name end) as source 
from order_info oi
left join client cl 
on oi.client_id = cl.id
where oi.status = "completed"
and oi.date > "2025-10-15"
and (oi.product_name = "Python" or oi.product_name = "Java" or oi.product_name = "C++") 
) as temp
where temp.cnt2 >= 2
group by temp.source
order by temp.source;

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

题目描述

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。

现在有简历信息表(resume_info)，部分信息简况如下:

第1行表示，在2025年1月2号，C++岗位收到了53封简历

。。。

最后1行表示，在2027年2月6号，C++岗位收到了231封简历

请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下:

解析:

第1行表示Python岗位在2025年2月收到了93份简历，在对应的2026年2月收到了846份简历

。。。

最后1行表示C++岗位在2025年1月收到了107份简历，在对应的2026年1月收到了470份简历

解题思路

看到这一道题的第一个想法就是自身内联接，然后过滤掉不同月份，并且第一个的date的年份在2025，第二个date的年份在2026，然后对job和两个date进行聚合就完成了。

事实证明还是太年轻了

自身内联接会产生出冗余的数据

内联接是先进行笛卡尔乘积，然后再进行过滤，假设这里有3条数据

进行上面所说的过滤，最后的结果为

问题就出现了，2026-10-01的数据出现了两次！

所以，上面的办法不行

既然同时查找两个不行，那能不能查一个然后再拼上另外一个？

查找一个的SQL很容易就可以写出来

//查找2025年的
select ri.job,
date_format(ri.date,"%Y-%m") as first_year_mon,
    ri.date ,
sum(ri.num) as first_year_cnt
from resume_info ri
where year(ri.date) = "2025"
group by ri.job,first_year_mon

//查找另一个2026年的
select ri.job,
date_format(ri.date,"%Y-%m") as second_year_mon,
    ri.date,
sum(ri.num) as second_year_cnt
from resume_info ri
where year(ri.date) = "2026"
group by ri.job,second_year_mon

现在的问题是如何进行拼接呢？

只要使用两个的job和对应的月份就可以拼接起来了

select temp1.job,temp1.first_year_mon,temp1.first_year_cnt,
temp2.second_year_mon,temp2.second_year_cnt
from (
select ri.job,
date_format(ri.date,"%Y-%m") as first_year_mon,
    ri.date ,
sum(ri.num) as first_year_cnt
from resume_info ri
where year(ri.date) = "2025"
group by ri.job,first_year_mon
) as temp1 
inner join(
    select ri.job,
    date_format(ri.date,"%Y-%m") as second_year_mon,
        ri.date,
    sum(ri.num) as second_year_cnt
    from resume_info ri
    where year(ri.date) = "2026"
    group by ri.job,second_year_mon
    ) as temp2 
    //使用job和month拼接起来
on temp1.job = temp2.job 
and month(temp1.date) = month(temp2.date)
    order by temp1.first_year_mon desc,temp1.job desc;

最差是第几名(二)

题目描述

TM小哥和FH小妹在牛客大学若干年后成立了牛客SQL班，班的每个人的综合成绩用A,B,C,D,E表示，90分以上都是A，80_{90分都是B，60}70分为C，50~60为D，E为50分以下

因为每个名次最多1个人，比如有2个A，那么必定有1个A是第1名，有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。

每次SQL考试完之后，老师会将班级成绩表展示给同学看。

现在有班级成绩表(class_grade)如下:

第1行表示成绩为A的学生有2个

…

最后1行表示成绩为D的学生有2个

老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出，以上例子查询结果如下:

解题思路

关键在于中位数怎么计算

看了题解，中位数有一个规律就是**某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数 **，累计是指前面的个数叠加起来（包括自己）

​ 比如:

​ A A B B C C D D

​ 1 2 3 4 5 6 7 8 正序排列

​ 8 7 6 5 4 3 2 1 逆序排列

​ 那么上面的4，5以及5，4就是中位数，如果是奇数的话，就只有1个

那么就好办多了

使用窗口函数就可以得到前面累计的个数得到正序，然后再DESC一波，就可以得到逆序了

那么还有一个总数问题怎么得到？这里应该是要每一行都要得到总数，要怎么实现？

由于聚合函数只能返回一行，所以这里我们使用子查询获取总数，来让所有行都拿到总数

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;