牛客SQL刷题记录

总结

on要和join连用

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

select*from employees order by hire_date desc limit 1;

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

ORDER BY 根据指定的列对结果集进行排序，默认按照升序，降序 ORDER BY DESC
LIMIT(m, n) 从第 m + 1 行开始取 n 条记录

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

看来sql说第几行是从1开始算的，limit 2,1表示从第2+1行开始，取1条记录

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

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1 offset 2;

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

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
order by salaries.emp_no;

select s.*,d.dept_no
from salaries as s  inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01'

select s.emp_no, salary, s.from_date, s.to_date, dept_no
from salaries s join dept_manager dm
on s.emp_no = dm.emp_no
and dm.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by s.emp_no

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

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

select s.last_name,s.first_name,d.dept_no from employees as s inner join dept_emp as d
where s.emp_no = d.emp_no;

省略了join可以用where，但是不能用on

select s.last_name,s.first_name,d.dept_no from employees as s , dept_emp as d
where s.emp_no = d.emp_no;

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

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

内联结，两边表同时有对应的数据，即任何一边缺失数据就不显示。
左联结，读取左边数据表的全部数据，即便右边表无对应数。即右表d中dept_no即使为NULL，也会读取左表e中的全部emp。

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

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

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

注意，having不能换成where,having用于分组之后的筛选

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

select distinct salary from salaries order by salary desc;

SQL10 获取所有非manager的员工emp_no

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

SELECT e.emp_no
FROM employees AS e
LEFT JOIN dept_manager AS d
ON e.emp_no=d.emp_no
WHERE dept_no IS NULL;

SQL11 获取所有员工当前的manager

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select s.emp_no ,d.emp_no as manager from dept_emp  s inner join dept_manager d
on s.dept_no=d.dept_no
and s.emp_no<>d.emp_no

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e LEFT JOIN dept_manager m 
ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no

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

第一步：每个部门最高的薪水

select 
  d.dept_no,max(s.salary) salary 
from 
  dept_emp d
join 
  salaries s
on 
  d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by 
  d.dept_no;

第二步(将员工、部门、薪水整合在一张表内)：

select 
  d.emp_no,d.dept_no,s.salary 
from 
  dept_emp d
join 
  salaries s
on 
  d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01';

.第三步：(将t1和t2整合)

select 
  t1.dept_no,t2.emp_no,t1.salary 
from 
  (表t1) t1
join 
  (表t2) t2
on 
  t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
  t1.dept_no;

select 
  t1.dept_no,t2.emp_no,t1.salary 
from 
  (select 
    d.dept_no,max(s.salary) salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
  group by 
    d.dept_no) t1
join 
  (select 
    d.emp_no,d.dept_no,s.salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01') t2
on 
  t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
  t1.dept_no;

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

SELECT *
FROM employees
WHERE emp_no%2=1
AND last_name NOT LIKE 'Mary'
ORDER BY hire_date DESC;

select *from employees where emp_no%2=1 and last_name!='Mary' Order by hire_date desc

select *from employees where MOD(emp_no, 2)=1 and last_name<>'Mary' Order by hire_date desc

补充：emp_no % 2=1也可以改成MOD(emp_no, 2)=1
补充：不相等有三种表示方式：<>、!=、IS NOT
注意：last_name是varchar类型，所以对它的判断需要加上单引号

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

查询奇数的一般方法：如上(最好是位运算&)
查询偶数的一般方法：emp_no=(emp_no>>1<<1)

select *from employees where emp_no=(emp_no>>1<<1) and last_name<>'Mary' Order by hire_date desc

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

注意：AVG(*)是自动命名为avg的，所以不用重命名

SELECT t.title, AVG(salary) 
FROM titles as t
INNER JOIN salaries as s
ON t.emp_no=s.emp_no
GROUP BY t.title;

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

limit的意思是取几条记录，limit 1,1就是越过第一条，取一条记录，即取第二条记录

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

考虑到工资第一多第二多的员工都有可能有多个，所以需要将其按照工资分组再排序
GROUP BY salary就是把所有相同的工资分成一组，然后降序，取第二组

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
                FROM salaries                
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1 )  

最高工资可能有多个人, 先拿到最高工资的值, 在把这个值当作条件查员工编号和工资

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

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

select e.emp_no,s.salary,e.last_name ,e.first_name from employees  e
inner join salaries  s 
on e.emp_no=s.emp_no
order by s.salary DESC
limit 1,1

但是题目不准使用order by

通过自连接和<=号配合COUNT函数找出排名高的薪水

SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
AND salary=(SELECT s1.salary
FROM salaries AS s1
INNER JOIN salaries AS s2
WHERE s1.salary <= s2.salary
GROUP BY s1.salary
HAVING COUNT(DISTINCT s2.salary)=2
);

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

注意是查所有员工，所以我们在连接表的时候不能用等值连接，因为这样会忽略掉空值，我用的左连接

select  last_name ,first_name,dept_name from
(select first_name,last_name,dept_no from employees e left join dept_emp d  on  e.emp_no=d.emp_no) s1 
left join departments  d2
on s1.dept_no= d2.dept_no

方法1：先内连接departments表和dept_emp表形成新表b，再左连接employees表和b表

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

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

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE s.to_date='9999-01-01') AS sCurrent
INNER JOIN (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
AND s.from_date=e.hire_date) AS sStart
ON sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

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

SELECT d.dept_no, d.dept_name, count(s.salary) AS SUM
FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no ASC