牛客网SQL编程题-更新到93题

第一题

查找最晚入职员工的所有信息

select * from employees order by hire_date desc limit 1 offset 0 
#按入职日期降序排列，取最大值，适用于每个员工入职时间不同
select * from employees where hire_date = (select max(hire_date) from employees
#选择入职时间为最晚的员工，时间越晚数值越大，适用于任何情况

第二题

查找employees里入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date DESC limit 1 offset 2
#同上题，注意剪切时从n-1开始
select * from employees where hire_date = 
(select distinct hire_date from employees order by hire_date DESC limit 1 offset 2)
#同上题，适用于入职时间不一定相同情况。其中distinct为查找不同的值

第三题

查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
order by salaries.emp_no
/*由于可能存在离职等情况，需加入
“dept_manager.to_date='9999-01-01'”两项*/
#运行出的结果与题中有差异，需继续讨论,好像是题答案里数据有问题
select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager
on salaries.emp_no=dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
#使用内联结，即保留并集

第四题

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

select employees.last_name,employees.first_name,dept_emp.dept_no
from dept_emp inner join employees
on dept_emp.emp_no=employees.emp_no
#内联结，取两个表的交集

第四题

查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left outer join dept_emp
on employees.emp_no=dept_emp.emp_no
#左联结。外联结可以其中部分数据为空，内联结必须两个表均有数据

第七题（五六丢了）

查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no,count(emp_no) as t from salaries
group by emp_no having t>15
#计数，设为新的变量。按某变量分组，分组条件为having不是where

第八题

找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select distinct salary from salaries
where to_date='9999-01-01' order by salary DESC;
#方法一，distinct只显示一次
select salary from salaries where to_date='9999-01-01'
group by salary order by salary DESC
#方法二，根据salary分组

第十题（九题没了）

找出所有非部门领导的员工emp_no

select employees.emp_no from employees left join dept_manager
on employees.emp_no=dept_manager.emp_no
where dept_no is null
#方法一，左外联结，再设仅存于领导表中的某变量为空，LEFT JOIN+IS NULL
select emp_no from employees where emp_no not in 
(select emp_no from dept_manager)
#方法二，使用NOT IN+子查询

第十一题

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select dept_emp.emp_no as emp_no,dept_manager.emp_no as manager from
dept_emp join dept_manager on dept_emp.dept_no=dept_manager.dept_no
where dept_emp.emp_no!=dept_manager.emp_no
#内连接+！=

第十二题

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

select dept_emp.dept_no as dept_no，
dept_emp.emp_no as emp_no,salaries.salary as salary
from dept_emp join salaries on
dept_emp.emp_no=salaries.emp_no
group by dept_no order by salary DESC limit 1 offset 0
#先在联结表中查找，然后分组，然后排序（降序），最后剪切
/*注意不能用having，HAVING语句用来与聚合函数联合使用，
过滤GROUP BY语句返回的记录集，筛选的是分组，
例如HAVING AVG(score)>=80，筛选的是那些平均成绩大于80的分组，
而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录。
这是错误的；*/
select r.dept_no,ss.emp_no,r.maxSalary from 
(select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries 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)as r,
salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
order by r.dept_no asc
/#这是为了避免当一个部门有多个最高值时

第十四题（十三题没了）

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees 
where emp_no%2=1 and last_name not like 'Mary'
order by hire_date DESC

第十五题

请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees 
where emp_no%2=1 and last_name not like 'Mary'
order by hire_date DESC

第十六题

统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

select t.title,avg(s.salary) from titles as t join salaries as s
on t.emp_no=s.emp_no
group by t.title order by avg(s.salary) ASC

第十七题

获取薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary from salaries
where salary=(select salary from salaries order by
salary DESC limit 1 offset 1)
#为了避免有人并列第二多，先查找薪水第二多的值

第十八题

查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

select e.emp_no,s.salary,e.last_name,e.first_name from
employees as e join salaries as s on e.emp_no=s.emp_no
where s.salary=(
select max(salary) from salaries
where salary<
(select max(salary) from salaries)
)

第十九题

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select last_name,first_name,dept_name FROM
employees as e left join dept_emp as de on e.emp_no=de.emp_no
left join departments as d on de.dept_no=d.dept_no
#三表联结

第二十一题（二十题没了）

查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select s1.emp_no,s1.salary-s2.salary as growth
from (select * from salaries where to_date='9999-01-01') as s1
left join (select * from salaries group by emp_no order by from_date)
as s2 on s1.emp_no=s2.emp_no
order by growth
/*先创建一个在职员工表s1，再创建一个入职员工表s2,
表s2为原始表中按工号分组，按工资初始时间排序，剪切
分组中，如果一组有多行，默认显示第一行*/

第二十二题

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

select d.dept_no,d.dept_name,count(*)
as sum from departments as d join dept_emp on d.dept_no=dept_emp.dept_no
join salaries as s on dept_emp.emp_no=s.emp_no
group by d.dept_no order by d.dept_no ASC
#注意各表的联结键

第二十三题

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列

select emp_no,salary,
dense_rank() over (order by salary DESC)
as t_rank from salaries
#使用窗口函数，相同并列排名dense_rank()

第二十四题

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

select de.dept_no,s.emp_no,s.salary
from employees as e,salaries as s,dept_emp as de,dept_manager
where e.emp_no=de.emp_no
and de.emp_no=s.emp_no
and s.emp_no=dept_manager.emp_no
and de.dept_no not in (select emp_no from dept_manager)
#做出来的答案是相反的，取出了manager里员工的数据。待查
SELECT de.dept_no, s.emp_no, s.salary