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