2018-04-12牛客SQL习题

1. 查找当前员工薪水并逆序输出

   
   题目详情
   

   参考答案：

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

group by 分组，相同的记录只显示一次
order by 排序，对过滤出的记录进行排序

2. 题目二

   
   题目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#这两个表中的字段不能相同

3. 题目三

   
   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#按部门分组

4. 题目四

   
   image.png

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

5. 题目五

   
   image.png

select title,count(distinct  emp_no) as t from titles#计算 title 的组数并忽略相同的emp_no
group by title 
having t >= 2

6. 题目六

   
   image.png

select emp_no ,salary from salaries 
where to_date='9999-01-01'#时间
order by salary desc#逆序排列
limit 1,1 #从第二个开始取一条记录