牛客网mysql题解及知识点记录

看清表名，列名。

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

select * from employees
# order by hire_date desc
# limit 1
where hire_date=(select max(hire_date) from employees)

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

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

(left join)

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

select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from dept_manager left join salaries on salaries.emp_no=dept_manager.emp_no
order by salaries.emp_no

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

select employees.last_name,employees.first_name,dept_emp.dept_no
from dept_emp left join employees on dept_emp.emp_no=employees.emp_no

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

(group by)

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left join dept_emp on dept_emp.emp_no=employees.emp_no

SQL6 记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no,t
from (select emp_no,count(salary) as t from salaries group by emp_no) result
where t>15

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

select salary
from salaries
group by salary
order by salary desc

SQL8 获取所有非manager的员工emp_no

select employees.emp_no
from employees left join dept_manager on employees.emp_no=dept_manager.emp_no
where dept_manager.dept_no is null

SQL9 获取所有员工当前的manager

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

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

（困难）
思路：先用子查询把每个部门和对应的最高工资找出来，然后基于这个结果到join后的原始两个表中查询工资数和部门都符合的。（如果不对原始两个表进行join则还需要在where中加一条判断emp_no即员工号是否匹配的条件）
不加distinct则最高工资对应多个员工都会显示

select distinct dept_emp.dept_no,salaries.emp_no,salaries.salary as maxSalary
from (select dept_emp.dept_no as max_dept,max(salaries.salary) as max_sa
     from dept_emp inner join salaries on dept_emp.emp_no=salaries.emp_no
     group by dept_emp.dept_no) as the_max,
     dept_emp inner join salaries on salaries.emp_no=dept_emp.emp_no
where the_max.max_sa=salaries.salary and the_max.max_dept=dept_emp.dept_no
order by dept_emp.dept_no

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

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

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

select titles.title,avg(salaries.salary)
from titles inner join salaries on titles.emp_no=salaries.emp_no
group by titles.title
order by avg(salaries.salary)

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

select emp_no,salary
from salaries
where salary=(select salary from salaries
             order by salary desc
             limit 1,1)

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary(不能使用order by完成)

（在读取表时设置别名简化代码）

select em.emp_no,sa.salary,em.last_name,em.first_name
from salaries as sa inner join employees as em on sa.emp_no=em.emp_no
where sa.salary=(select max(salary) from salaries 
                 where salary < (select max(salary) from salaries))

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

(可以连续join多个表)

select em.last_name,em.first_name,dep.dept_name
from employees as em left join dept_emp as de on de.emp_no=em.emp_no
     left join departments as dep on de.dept_no=dep.dept_no

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

(困难)

select beg_tab.emp_no,(end_tab.salary-beg_tab.salary) as growth
from (select emp_no,salary
      from salaries as sa
      where sa.to_date='9999-01-01') as end_tab inner join
      (select sa.emp_no as emp_no,sa.salary as salary
      from salaries as sa inner join employees as em on sa.emp_no=em.emp_no
      where sa.from_date=em.hire_date) as beg_tab 
      on end_tab.emp_no=beg_tab.emp_no
order by end_tab.salary-beg_tab.salary

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

select dep.dept_no,dep.dept_name,count(sa.salary)
from salaries as sa left join dept_emp as de_em on sa.emp_no=de_em.emp_no
     left join departments as dep on de_em.dept_no=dep.dept_no
group by dep.dept_no
order by dep.dept_no

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

窗口函数
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 <排序用列清单> ）

• PARTITION BY 表示将数据先按 part_list 进行分区
• ORDER BY 表示将各个分区内的数据按 order_list 进行排序
• *其中[ ]中的内容可以忽略
  (SQL60、63也是窗口函数)

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

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

(1.员工对应部门要在dept_emp中获取 2.有员工没有分配部门的情况)

select dep.dept_no as dept_no,
       sa.emp_no as emp_no,
       sa.salary as salary
from salaries as sa 
    left join dept_emp as dep on sa.emp_no=dep.emp_no
    left join dept_manager as ma on sa.emp_no=ma.emp_no
where ma.dept_no is null and dep.dept_no is not null

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

select dep.emp_no,man.emp_no as manager_no,emp_sa.salary as emp_salary,man_sa.salary as manager_salary
from dept_emp as dep left join
     dept_manager as man on dep.dept_no=man.dept_no left join
      (select emp_no,salary from salaries) as emp_sa on emp_sa.emp_no=dep.emp_no left join
      (select emp_no,salary from salaries) as man_sa on man_sa.emp_no=man.emp_no
where emp_sa.salary>man_sa.salary

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

• 为什么group by不需要dep.dept_name?

因为：当select的是某表的主键/唯一性字段，那么select的这个表的其他列不用在group by出现，因为此时mysql能识别出其他列是依赖于这个主键的。

select dep.dept_no,dep.dept_name,titles.title,count(titles.emp_no)
from departments as dep left join
     dept_emp on dep.dept_no=dept_emp.dept_no left join
     titles on dept_emp.emp_no=titles.emp_no
group by dep.dept_no,titles.title
order by dep.dept_no

SQL 27

1.查找描述信息中包括robot的电影对应的分类名称以及电影数目
2.还需要该分类对应电影数量>=5部

select ca.name,count(fc.film_id)
from (select * from film where description like '%robot%') as film inner join
     (select * from film_category where category_id in (select category_id 
      from film_category 
      group by category_id
      having count(film_id)>=5)) as fc on fc.film_id=film.film_id left join
     category as ca on ca.category_id=fc.category_id

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

select film.film_id,film.title
from film left join film_category on film.film_id=film_category.film_id
where film_category.category_id is null

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

select title,description
from film
where film_id in (select fc.film_id 
                  from film_category as fc inner join
                  category as ca on fc.category_id=ca.category_id
                  where ca.name='Action')

（从这里往后基本上都是科普函数用法）

SQL31 将employees表的所有员工的last_name和first_name拼接起来

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

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

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

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 if not exists actor_name(
                        first_name varchar(45) not null, #声明字段
                        last_name varchar(45) not null);
insert into 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 (first_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

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

• MYSQL中强制索引查询使用：FORCE INDEX(indexname);
• SQLite中强制索引查询使用：INDEXED BY indexname;

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

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

• 插入一行内容用Insert into 表名 （‘列标签（可省略）’） VALUES(‘行内容’)
• 插入一列 用 alter table 表名 add（‘列的属性’）
• MySQL中default后不需要加括号。

alter table actor
add
create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00';

SQL41 构造一个触发器audit_log

• 1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER来指定在执行后面的SQL语句之前或之后来触发TRIGGER
• 2、触发器执行的内容写出 BEGIN与END 之间
• 3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

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对应的记录

报错：SQL_ERROR_INFO: “You can’t specify target table ‘titles_test’ for update in FROM clause”
原因：不能先select出同一表中的某些值，然后在同一语句中更改这个表，UPDATE或DELETE中子查询不能为同一张表。
解决方法：把用到titles_test这个表的查询作为中间表，给这个表添加别名就可以了。

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

正确代码：
在MySQL中需要给子查询添加别名，不然会抛出错误：ERROR 1248 (42000): Every derived table must have its own alias

delete from titles_test
where id not in(select * from(
select min(id) from titles_test group by emp_no) as 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 不用update将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

写法一：

REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

写法二：不必知道id=5其他列具体值

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 = 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通过(’)连接起来。

(sqlite不支持concat，请用||实现，mysql支持concat)
考察引号中使用引号。

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

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

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

SQL52 获取Employees中的first_name

（字符串截取）

#方法一，right()
select first_name from employees order by right(first_name, 2)
#方法二，substr()
select first_name from employees order by substr(first_name, -2)
#or    
select first_name from employees order by substr(first_name, length(first_name)-1);

SQL53 按照dept_no进行汇总

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

SQL54 查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary

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

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

select *
from employees
limit 5,5

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

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

exists写法：

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

in写法：

select *
from employees
where emp_no not in (select emp_no from dept_emp)

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

(case when)

select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
       (case b.btype
            when 1 then s.salary*0.1
            when 2 then s.salary*0.2
            else s.salary*0.3 end) bonus
from employees e inner join
     emp_bonus b on e.emp_no=b.emp_no inner join
     salaries s on e.emp_no=s.emp_no
where 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'

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

这题比较奇葩。题目要求不排序，因此输出顺序要和原表一致。。。
所以得把自查询的表和原表join一下才行。如果不要求这个顺序，只用子查询就可以。

select r.first_name
from employees e join
    (select first_name,
             rank() over(order by first_name) as ran
      from employees) r on e.first_name=r.first_name
where r.ran%2=1

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

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

SQL63 刷题通过的题目排名

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

SQL64 找到每个人的任务

两个表相同含义列名字不同，相同列名实际含义不同的情况。

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

SQL65 异常的邮件概率

统计每一天异常邮件数量；和原表链接；统计每天邮件总数量；异常/总数。
注意：子查询和总表中都要限制收发均非黑名单用户。

select e.date,round(c.num/count(e.id),3)
from email e  left join 
    (select date,count(id) num
      from email
      where type='no_completed' and
            send_id in (select id from user where is_blacklist=0) and
            receive_id in (select id from user where is_blacklist=0)
      group by date) c
     on e.date=c.date
where send_id in (select id from user where is_blacklist=0) and
      receive_id in (select id from user where is_blacklist=0)
group by e.date

更简便的方法。
（用case when把标识转化为整型，从而可以使用聚合函数sum统计异常邮件数量）

select date,
       round(sum(case e.type when 'no_completed' then 1 else 0 end)/count(e.type),3) as p 
from email e
where send_id in (select id from user where is_blacklist = 0)
and receive_id in (select id from user where is_blacklist = 0)
group by date
order by date

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

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

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

条件判断in的前后维数要一致。

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

SQL68 牛客每个人最近的登录日期(三)：计算次日留存率

找到次日登陆用户数；除以总用户数。

select round((select count(user_id)
       from login
       where (user_id,date) in 
                (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY)
                from login
                group by user_id))/count(distinct user_id),3)
from login

思路一样，更优雅的写法。把冗长的子查询拿出来，变成主查询。

select round(count(user_id)
             /(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 牛客每个人最近的登录日期(四)：每日新用户数

思路一：
找到每个用户首次登陆后时间；然后与全部时间链接（不能省，否则会缺少没有用户在当天首次登陆的日期）；然后按全部时间分组统计。

select l.d_date,count(new_l.user_id)
from (select distinct date d_date from login) l left join
     (select user_id,min(date) min_d
      from login
      group by user_id) new_l on l.d_date=new_l.min_d
group by l.d_date

思路二：case when
新建一列，当前日期为当前用户首次登陆为1，否则为0；然后按日期分组统计。

select date,sum(
	   case 
		   when (user_id,date) in 
			 (select user_id,min(date) from login group by user_id) then 1
           else 0 end) as new
from login
group by date
order by date

SQL70 牛客每个人最近的登录日期(五)：每天的次日留存率

找到有新用户的日期以及对应新用户，并与所有日期join；使用case when计数新用户次日留存数作为分子；除以每日新用户数；使用ifnull()函数将None值转化为0。

select l.d_date,
       ifnull(round(
       sum(case 
               when (new_l.user_id,DATE_ADD(new_l.new_d,INTERVAL 1 DAY)) in
                    (select user_id,date from login) then 1
               else 0 end)/count(new_l.user_id),3),0) p
from (select distinct date d_date from login) l left join
     (select user_id,min(date) new_d from login
      group by user_id) new_l on l.d_date=new_l.new_d
group by l.d_date

SQL71 牛客每个人最近的登录日期(六)：用户每天的累积刷题数

select name,date,
       sum(number) over(partition by user_id order by date)
from passing_number p left join 
     user u on p.user_id=u.id
order by date,name

SQL72 考试分数(一)：各科目平均分

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

SQL73 考试分数(二)：自己得分高于该科目平均分的用户信息

解法1：子查询
内表可以查到外表变量。order by 1表示按照select的第一列排序。

select id,job, score 
from grade t1
where score >
  (select avg(score) from grade t2 where t1.job = t2.job)
order by 1;

解法2：窗口函数
直接用开窗函数，从原表算出平均值作为新的一列

select id, job, score
from
  (select id,job,score,
    avg(score)over(partition by job)av
   from grade
  )t1
where score > av
order by 1 ;

解法3：表连接
从原表算出均值之后，用连接的方式与新表合并

select g.id,g.job,g.score
from grade g left join (select job,avg(score) score from grade 
group by job) av on g.job=av.job
where g.score>av.score
order by id

SQL74 考试分数(三):每门考试的前两名

允许并列：dense_rank
"r<3"的条件只能在主查询里：查询中select最后执行，也就是子查询执行此判断语句时还并没有执行开窗函数；如果在表连接时使用，则r<3的行依然会存在，只是不会与语言名表连接，会导致最终结果中出现语言名为None的结果。

select t1.id,t2.name,t1.score
from (select id,language_id,score,
   dense_rank() over(partition by language_id order by score desc) r
   from grade) t1 
   left join language t2 on t1.language_id=t2.id
where r<3
order by t2.name,t1.score desc,t1.id

SQL75 考试分数(四):每门考试中位数位置范围

round()函数是四舍五入的。

select job,round(count(score)/2,0),round((count(score)+1)/2,0)
#        (case when count(score)%2=1 then round(count(score)/2,0)
#                   else round(count(score)/2+1,0) end)
from grade
group by job
order by job