牛客-Mysql实战-按热度排序(前20）

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

做题笔记:
limit关键字可以从查询结果中取部分值。首先要定义两个参数,offset,size;
offset：是开始读取的第一条记录的编号(注意:在查询结果中，第一个记录的编号是0而不是1)
size：查询记录的个数

#------------------------

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

笔记:OJ的数据应该是没有重复的

#-----------------------

select tb1.*,tb2.dept_no
from salaries as tb1
left join dept_manager as tb2
on tb1.emp_no=tb2.emp_no
where tb1.to_date='9999-01-01'
and tb2.to_date='9999-01-01';

#--------------------

select last_name,first_name,tb2.dept_no
from employees as tb1
inner join dept_emp as tb2
on tb1.emp_no=tb2.emp_no;

#----------------------

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

做题笔记:
查询的内容包括未分配部门的员工，所以应该以employees表为主表,则查询的部门为:交集+主表有而从表没有 的信息，故使用左外连接

#*-----------------------------

select tb1.emp_no,tb2.salary
from employees as tb1
inner join salaries as tb2
on tb1.emp_no=tb2.emp_no
and tb1.hire_date=tb2.from_date
order by tb1.emp_no desc;

#-------------------------------

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

做题笔记:
题目的存在bug，不一定是存在15条工资记录就代表涨幅15次T_T，这…

#-------------------------

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

#-------------------------------

select tb2.dept_no,tb2.emp_no,tb1.salary
from salaries as tb1
inner join dept_manager as tb2
on tb1.emp_no=tb2.emp_no
where tb1.to_date='9999-01-01'
and tb2.to_date='9999-01-01';

#-------------------------

解法一:

select tb1.emp_no from employees as tb1
where tb1.emp_no not in (select emp_no from dept_manager);

解法二:

select tb1.emp_no
from employees as tb1
left join dept_manager as tb2
on tb1.emp_no=tb2.emp_no
where tb2.emp_no is null;

select tb1.emp_no,tb2.emp_no as namager_no
from dept_emp as tb1
inner join dept_manager as tb2
on tb1.dept_no=tb2.dept_no
and tb1.emp_no<>tb2.emp_no
where tb1.to_date='9999-01-01'
and tb2.to_date='9999-01-01';

#*----------------

select dept_no,tb1.emp_no,max(salary) as salary
from dept_emp as tb1
inner join salaries as tb2
on tb1.emp_no=tb2.emp_no
where tb1.to_date='9999-01-01'
and tb2.to_date='9999-01-01'
group by dept_no;

#------------------

select title,count(*) as t
from titles
group by title;

#-------------------*

select title,count(distinct emp_no)
from titles
group by title;

#*-------------------------

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

#*--------------------------

select title,avg(salary) as avg
from titles as tb1
left join salaries as tb2
on tb1.emp_no=tb2.emp_no
where tb1.to_date='9999-01-01'
and tb2.to_date='9999-01-01'
group by title;

##*--------------------

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

#*----------------------

#最高工资
select max(salary) from salaries where to_date='9999-01-01'

#除去最高工资后
select * from salaries where salary<>(select max(salary) from salaries where to_date='9999-01-01')

#当前表的最高工资
select max(salary) from 
(select * from salaries where salary<>(select max(salary) from salaries where to_date='9999-01-01'))
where to_date='9999-01-01'

#完整代码
select tb1.emp_no,salary,last_name,first_name
from employees as tb1
inner join salaries as tb2
on tb1.emp_no=tb2.emp_no
and tb2.to_date='9999-01-01'
and
salary=(select max(salary) from salaries where salary <>(select max(salary) from salaries))

#-*******************

#三表连接
select last_name,first_name,dept_name
from employees as tb1
left join dept_emp as tb2
on tb1.emp_no=tb2.emp_no
left join departments as tb3
on tb2.dept_no=tb3.dept_no;

笔记:
注意题干中的也**包括没有分配部门的员工*
*

#-----------------------------

select max(salary)-min(salary) as growth
from salaries
where emp_no=10001