牛客SQL（15-18）

15 查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

解法一

select * from employees
where emp_no%2=1
and last_name != ‘Mary’
order by hire_date desc;

解法二 （使用位运算）

select * from employees
where emp_no&1=1
and last_name != ‘Mary’
order by hire_date desc;
奇数&1=1
偶数&1=0
另外,判定是否为偶数,可以通过先除以2再乘以2
emp_no >> 1 << 1 是否等于emp_no

16 统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

select t.title,avg(s.salary) from
titles t inner join salaries s
on t.emp_no = s.emp_no
where t.to_date=‘9999-01-01’
and s.to_date=‘9999-01-01’
group by t.title;

17 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

解法一 distinct

select emp_no , salary from salaries
where to_date=‘9999-01-01’
and salary =
(select distinct salary from salaries
order by salary desc
limit 1,1);

解法二 group by

select emp_no , salary from salaries
where to_date=‘9999-01-01’
and salary =
(select salary from salaries
group by salary
order by salary desc
limit 1,1);

18 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

去掉最大值再取最大值。
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
where salary =
(
select max(salary) from salaries
where salary<(select max(salary) from salaries
where to_date=‘9999-01-01’)
);