MYSQL
查找当前薪水排名第二多的员工
查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗
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
));
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
));
/*
嵌套子查询+聚合函数
*/
select s.emp_no, s.salary, e.last_name, e.first_name
from employees e 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
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and to_date = '9999-01-01'
select s.emp_no, s.salary, e.last_name, e.first_name
from employees e join salaries s
on e.emp_no = s.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2
on s1.salary <= s2.salary
and s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.salary
having count(distinct s2.salary) = 2
)
and s.to_date = '9999-01-01'
/*
以s1进行分组，每个组中数字的个数，就是s1排的名次
*/
对所有员工的薪水按照salary进行按照1-N的排名
有一个薪水表salaries简况如下:
对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列：
select emp_no, salary, dense_rank() over (order by salary desc) as t_trank
from salaries
order by t_trank, emp_no
/*
窗口函数
*/
select s1.emp_no, s1.salary, count(distinct s2.salary) as t_trank
from salaries s1 join salaries s2
on s1.salary <= s2.salary
group by s1.emp_no
order by s1.salary desc, s1.emp_no
获取所有非manager员工当前的薪水情况
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
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
));
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
));
select de.dept_no, de.emp_no, s.salary
from salaries s, dept_emp de
where s.emp_no = de.emp_no and s.emp_no not in (
select emp_no
from dept_manager
where to_date = '9999-01-01'
) and s.to_date = '9999-01-01'
获取员工其当前的薪水比其manager当前薪水还高的相关信息
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
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
));
select de.emp_no, dm.emp_no manager_no, s1.salary emp_salary, s2.salary manager_salary
from dept_emp de, dept_manager dm, salaries s1, salaries 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
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
查找描述信息中包含robot的电影对应的分类名称以及电影数目
题目描述
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));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL,
last_update
timestamp,
PRIMARY KEY ( category_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部.
注：电影数目>=5 是这类电影的所有数目，并不是包含了robot的这类电影的数目
select c.name `分类名称category.name`, count(f.film_id) as `电影数目count(film.film_id)`
from film f, category c, film_category fc
where f.description like '%robot%'
and f.film_id = fc.film_id
and c.category_id = fc.category_id
and c.category_id in
(select category_id
from film_category
group by category_id
having count(film_id) >= 5
)
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
emp_bonus
(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);
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
));
select e.emp_no `e.emp_no`, de.dept_no, eb.btype, eb.received
from emp_bonus eb right join employees e
on e.emp_no = eb.emp_no, dept_emp de
where e.emp_no = de.emp_no
获取有奖金的员工相关信息
获取有奖金的员工相关信息。
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
));
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
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、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
select eb.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (s.salary * eb.btype * 0.1) bonus
from emp_bonus eb left join employees e on eb.emp_no = e.emp_no, salaries s
where eb.emp_no = s.emp_no and s.to_date = '9999-01-01'
统计salary的累积和
按照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
));
窗口函数
select emp_no, salary, sum(salary) over (order by emp_no) running_total
from salaries
where to_date = '9999-01-01'
select s1.emp_no, s1.salary, sum(s2.salary) running_total
from salaries s1 inner join salaries s2
on s2.emp_no <= s1.emp_no
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
对于employees表中，输出first_name排名为奇数的first_name
对于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’);
select e1.first_name
from employees e1
where
(
select count(*)
from employees e2
where e1.first_name >= e2.first_name
) % 2 = 1
异常邮件的概率
现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(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 e.date, round(
sum(case e.type when 'completed' then 0 else 1 end) * 1.0 / count(e.type),
3) as p
from email e, `user` u1, `user` u2
where e.send_id = u1.id
and e.receive_id = u2.id
and u1.is_blacklist = 0
and u2.is_blacklist = 0
group by e.date
order by e.date
/*
round函数用于四舍五入
round(x,d) ，x指要处理的数，d是指保留几位小数
注：d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
round(x) ,其实就是round(x,0),也就是默认d为0；
*/
/*
case...when... then... else... end
*/
牛客每个人最近的登录日期（二）
牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.
有一个登录(login)记录表，简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
还有一个用户(user)表，简况如下:
还有一个客户端(client)表，简况如下:
请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:
查询结果表明:
fh最近的登录日期在2020-10-13，而且是使用pc登录的
wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的
select u.name u_n, c.name c_n, l.date
from login l, `user` u, client c
where l.user_id = u.id
and l.client_id = c.id
and l.date = (select max(l1.date) from login l1 where l.user_id = l1.user_id)
order by u_n
牛客每个人最近的登录日期（三）
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
第4行表示id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
。。。
最后1行表示id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:
查询结果表明:
id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
固次日成功的留存率为 2/4=0.5
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)
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)
牛客每个人最近的登录日期（四）
牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
。。。
第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
。。
最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:
查询结果表明:
2020-10-12，有3个新用户(id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(id为4)登录
2020-10-15，没有新用户登录
select a.date, sum(case when tmp = 1 then 1 else 0 end) new
from
(select `date`, row_number() over (partition by user_id order by `date`) tmp from login) a
group by `date`
/*
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
partition by 与group by不同之处在于前者返回的是分组里的每一条数据，
并且可以对分组数据进行排序操作。
后者只能返回聚合之后的组的数据统计值的记录。
*/
牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
有一个登录(login)记录表，简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第5行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
有一个刷题（passing_number)表，简况如下:
第1行表示id为2的用户在2020-10-12通过了4个题目。
。。。
第3行表示id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示id为4的用户在2020-10-13通过了2个题目
还有一个用户(user)表，简况如下:
请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:
查询结果表明:
fh在2020-10-12为止，总共通过了4道题，输出为4
wangchao在2020-10-12为止，总共通过了1道题，总计为1
tm在2020-10-12为止只登陆了没有刷题，故没有显示出来
tm在2020-10-13为止刷了题，但是却没有通过任何题目，总计为0
wangchao在2020-10-13通过2道，但是加上前面2020-10-12通过1道，故在2020-10-13为止总共通过了3道题，总计为3
select u.name u_n, `date`, sum(number) over (partition by u.id order by `date`) ps_num
from passing_number pn left join user u
on pn.user_id = u.id
order by `date`, u_n
牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:
第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
…
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，
不同的语言岗位(language)表简化如下:
请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
select g.id, l.name, g.score
from (
select *, dense_rank() over (partition by language_id order by score desc) as tmp
from grade
) g, `language` l
where g.language_id = l.id and g.tmp <= 2
order by l.name, g.score desc, g.id
考试分数（四）
牛客每次考试完，都会有一个成绩表(grade)，如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:
解释:
第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)
第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)
第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为B语言岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数)
//中位数
select job, round(count(id) / 2) `start`, round((count(id) + 1) / 2) `end`
from grade
group by job
order by job
MYSQL取整函数
ROUND(X) – 表示将值 X 四舍五入为整数，无小数位
ROUND(X,D) – 表示将值 X 四舍五入为小数点后 D 位的数值，D为小数点后小数位数。若要保留 X 值小数点左边的 D 位，可将 D 设为负值。
FLOOR(X)表示向下取整，只返回值X的整数部分，小数部分舍弃。
CEILING(X) 表示向上取整。