牛客网MySQL在线编程

题目：牛客网MySQL在线编程

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

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

# 下面是错误的代码
# select * from employees where (hire_date = select max(hire_date) from employees)

错误原因：

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

select * from employees 
order by hire_date desc
limit 1 offset 2;


# 下面是错误代码
# select * from employees 
# where 
# group by hire_date
# order by hire_date desc
# limit 1 offset 2

错误原因：

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

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

SELECT d.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
FROM dept_manager d
JOIN salaries s
ON d.emp_no = s.emp_no
ORDER BY s.emp_no

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

SELECT e.last_name, e.first_name, d.dept_no
FROM `dept_emp` d
JOIN employees e
ON d.emp_no = e.emp_no

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

SELECT e.last_name, e.first_name, d.dept_no
FROM employees e
LEFT JOIN dept_emp d
ON e.emp_no = d.emp_no

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

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

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

SELECT DISTINCT(salary) 
FROM salaries
ORDER BY salary DESC

SQL10. 获取所有非manager的员工emp_no          

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

# 下面是错误代码
select e.emp_no 
from employees e
join dept_manager d
on e.emp_no != d.emp_no;

输出结果：

10001
10001
10002
10003

# 下面是错误代码
select e.emp_no 
from employees e
join dept_manager d
on e.emp_no not in d.emp_no;

SQL11. 获取所有员工当前的 manager

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e, dept_manager m
WHERE e.emp_no != m.emp_no
AND e.dept_no = m.dept_no

 

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

SELECT d.dept_no, d.emp_no, MAX(s.salary) AS maxSalary
FROM dept_emp d, salaries s
WHERE d.emp_no = s.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no

 

 SQL13.查找 employees 表中 emp_no 为奇数并且 last_name 不为 Mary 的员工信息，并按照 hire_date 逆序排列

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

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

select emp_no, salary, DENSE_rank () OVER ( ORDER BY salary DESC ) t_rank
from salaries
order by t_rank, emp_no

下面是错误代码：

select emp_no, salary, DENSE_rank () OVER ( ORDER BY salary DESC ) t_rank
from salaries
order by emp_no

错位原因：为什么按emp_no升序排序错误？