MYSQL(3)

最新推荐文章于 2024-06-22 15:26:37 发布

weixin_43947653

最新推荐文章于 2024-06-22 15:26:37 发布

阅读量182

点赞数

分类专栏：牛客SQL篇文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43947653/article/details/113618795

版权

牛客SQL篇专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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) 表示向上取整。

weixin_43947653

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
MYSQL(3)

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,
复制链接

扫一扫