SQL 实战 牛客网 20/61题

1.
select * from employees order by hire_date desc limit 1;

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

3.
select a.*,b.dept_no from salaries as a inner join dept_manager as b on a.emp_no=b.emp_no where a.to_date='9999-01-01' and 
b.to_date='9999-01-01' order by emp_no; 
//此题牛客代码编辑器一直报错，不明原因

4.
select b.last_name,b.first_name,a.dept_no from dept_emp as a inner join employees as b on a.emp_no=b.emp_no;

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

6.
select a.emp_no,b.salary from employees as a inner join salaries as b 
on a.emp_no=b.emp_no and a.hire_date=b.from_date 
order by a.emp_no desc;
// sql 语句其实有很多写法 ，可以只针对 b 分组查询。我倾向使用join.
SELECT
    emp_no,
    salary 
FROM
    salaries 
GROUP BY
    emp_no 
HAVING
    min( from_date ) 
ORDER BY
    emp_no DESC

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

8.
select distinct salary from salaries where to_date='9999-01-01' order by salary desc

9.
select b.dept_no,b.emp_no,a.salary from salaries as a left join dept_manager as b on a.emp_no=b.emp_no 
where a.to_date='9999-01-01' and b.to_date='9999-01-01';

10.
select a.emp_no from employees as a left join dept_manager as b on a.emp_no=b.emp_no where b.emp_no is null;

11.
select a.emp_no,b.emp_no as manager_no from dept_emp as a left join dept_manager as b 
on b.dept_no=a.dept_no where a.emp_no!=b.emp_no and b.to_date='9999-01-01';
// 题目限制条件太多， 容易看漏

12.
不会！！ 

MySQL 报错的答案能通过所有测试用例 ， 服气！

13.
select title,count(*) as t from titles group by title having t >= 2;


14.
select title,count(*) as t from 
(select distinct emp_no,title from titles)
group by title having t >= 2;

15.
select * from employees where (emp_no%2)==1 and last_name!='Mary' order by hire_date desc;

16.
select a.title, avg(b.salary) as avg 
from salaries as b inner join titles as a
on a.emp_no=b.emp_no 
where b.to_date='9999-01-01' and a.to_date='9999-01-01' 
group by a.title;

17.
select emp_no,salary from salaries where to_date='9999-01-01' order by salary desc limit 1,1

18.
select a.emp_no, b.salary, a.last_name, a.first_name
from employees as a inner join salaries as b on a.emp_no=b.emp_no
where b.to_date='9999-01-01' 
and b.salary = (select max(s.salary) from salaries as s where s.salary < (select max(salary) from  salaries) ) 


19.
select a.last_name,a.first_name,b.dept_name
from employees as a left join 
(select c.emp_no,c.dept_no,d.dept_name from dept_emp as c left join departments as d 
 on c.dept_no=d.dept_no) as b 
on a.emp_no=b.emp_no;

20.
select Max(salary)-Min(salary) as growth from salaries where emp_no=10001 group by emp_no