-
查找当前员工薪水并逆序输出
题目详情
参考答案：
select salary from salaries where to_date = '9999-01-01' group by salary order by salary desc;
group by 分组，相同的记录只显示一次
order by 排序，对过滤出的记录进行排序
-
题目二
题目2
select dept_emp.emp_no,dept_manager.emp_no as manager_no from dept_emp inner join dept_manager #连接两个表进行查询
where dept_emp.dept_no=dept_manager.dept_no#经理和自己的部门相同
and dept_emp.to_date=dept_manager.to_date
and dept_emp.to_date='9999-01-01'#时间
and dept_emp.emp_no <> dept_manager.emp_no#这两个表中的字段不能相同
-
题目三
image.png
select d.dept_no,s.emp_no,max(s.salary) as salary from dept_emp as d inner join salaries as s
where d.emp_no=s.emp_no #两张表的人员号相同
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'#时间为当前
group by d.dept_no#按部门分组
-
题目四
image.png
select title,count(title) as t from titles
group by title
having t >= 2
-
题目五
image.png
select title,count(distinct emp_no) as t from titles#计算 title 的组数并忽略相同的emp_no
group by title
having t >= 2
-
题目六
image.png
select emp_no ,salary from salaries
where to_date='9999-01-01'#时间
order by salary desc#逆序排列
limit 1,1 #从第二个开始取一条记录
