牛客题霸 11-20

具体题目参考链接:https://www.nowcoder.com/ta/sql


11. 获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no

思路:

  • 一个部门对应一个manager,所以用 e.dept_no = m.dept_no 为条件连接两表,可以得到每个员工对应部门的manager是谁
  • 如果当前的manager是自己的话结果不显示,所以需要判断 e.emp_no <> m.emp_no
  • 注意两个“当前”,即 e.to_date = ‘9999-01-01’ and m.to_date = ‘9999-01-01’
select e.emp_no, m.emp_no as manager
from dept_emp as e join dept_manager as m
on e.dept_no = m.dept_no
where e.emp_no != m.emp_no and e.to_date = '9999-01-01' and m.to_date = '9999-01-01';

12. 获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,结果按照部门升序排列

推荐阅读:如何正确理解SQL关联子查询

思路一:用相关子查询,子查询每次仅对一个部门内的员工进行处理。首先,子查询找到父查询中第一个部门最高的salary,然后,父查询找到该部门中所有薪水等于该最高salary的员工,接着子查询继续寻找下一个部门的最高salary,重复循环。

select de.dept_no, de.emp_no, s.salary
from dept_emp de join salaries s
on de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where s.salary = (select max(s2.salary)
              from dept_emp de2 join salaries s2
              on de2.emp_no = s2.emp_no and de2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
              where de2.dept_no = de.dept_no)
order by de.dept_no

思路二:用窗口函数,先在子查询中对组内的员工薪水进行排名。然后在父查询中选择 rank 为1的员工。

tips:窗口函数是在 WHERE 子句之后执行,所以必须要先把窗口函数的 SELECT 作为子查询,然后在父查询中写 WHERE a.rank = 1。假如只用一层查询,会报错。

SELECT a.dept_no, a.emp_no, a.salary AS maxSalary
FROM (
    SELECT d.emp_no, d.dept_no, s.salary,
    DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS rank
    FROM dept_emp d, salaries s
    WHERE d.emp_no = s.emp_no
) a
WHERE a.rank = 1;

下面是第一种错误解法,MAX(SALARY) 和 emp_no 不一定对应!因为 GROUP BY 默认取非聚合的第一条记录


SELECT d.dept_no, s.emp_no, max( s.salary ) AS salary 
FROM salaries s JOIN dept_emp d 
ON s.emp_no = d.emp_no 
WHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01'
GROUP BY de.dept_no
ORDER BY de.dept_no;

下面是第二种错误解法,如果不用相关子查询,直接用 where s.salary in,会出现:B部门的最高薪水在A部门中也出现了,但却不是A部门的最高薪水,则下面这种解法会把A部门中该薪水对应的员工也查询出来。

select de.dept_no, de.emp_no, s.salary
from dept_emp de join salaries s
on de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where s.salary in (select max(s2.salary)
              from dept_emp de2 join salaries s2
              on de2.emp_no = s2.emp_no and de2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
              group by de2.dept_no)
order by de.dept_no;

13. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t

思路:使用 group by 将title分组,再用 having 子句找出大于等于2的组,having 必须在 group by 之后,并且有 group by 才能使用 having。

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

14. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略

思路:使用 distinct 进行去重

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

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

思路:员工号为奇数,则emp_no取余应为1,last_name不为Mary,用‘!=’表示,另外注意字符串要用单括号括起来,逆序用desc。

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

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

思路:对两表进行内连接,然后按 title 分组。

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;

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

思路一:子查询中,使用 group by 对 salary 去重,然后用 order by 排序,从而找到排名第二的薪水数 salary,然后在主查询中,查询所有等于该 salary 的员工信息。

limit m, n:表示从下标m (下标从0开始)开始,取 n 条数据

select emp_no, salary
from salaries
where salary = (
    select salary
    from salaries
    group by salary
    order by salary desc
    limit 1, 1
);

下面是错误解法,要考虑到同一薪水有多人,例如薪水最高的有3人;薪水第二高的有2人

select emp_no, salary
from salaries
order by salary desc
limit 1, 1

思路二:
先在工资表里找出最多工资,再以工资小于最大工资为限定条件,即salary < (select max(salary)可以得到排名第二的工资数。
再使用max(salary)在 除去排名第一工资 的表里找最大的工资,即找到排名第二的工资。

select emp_no, salary
from salaries
where salary = (
    select max(salary)
    from salaries
    where salary < (select max(salary) from salaries)
);

18. 查找排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by

思路:本题在上一题的思路二的基础上,多了个内连接。

select e.emp_no, max(s.salary) as 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)
);


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

思路:关键就是要理清三个表之间的关系。题目要求包括暂时没有分配部门的员工,首先将 employees 作为主表,与 dept_emp 左连接,得到每个员工的部门号。然后以上一步得到的结果表作为主表,与 departments 左连接,得到每个部门号的 dept_name

select e.last_name, e.first_name, dp.dept_name
from (employees as e left join dept_emp as de on e.emp_no = de.emp_no)
left join departments as dp
on de.dept_no = dp.dept_no;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
牙科就诊管理系统利用当下成熟完善的SSM框架,使用跨平台的可开发大型商业网站的Java语言,以及最受欢迎的RDBMS应用软件之一的Mysql数据库进行程序开发。实现了用户在线查看数据。管理员管理病例管理、字典管理、公告管理、药单管理、药品管理、药品收藏管理、药品评价管理、药品订单管理、牙医管理、牙医收藏管理、牙医评价管理、牙医挂号管理、用户管理、管理员管理等功能。牙科就诊管理系统的开发根据操作人员需要设计的界面简洁美观,在功能模块布局上跟同类型网站保持一致,程序在实现基本要求功能时,也为数据信息面临的安全问题提供了一些实用的解决方案。可以说该程序在帮助管理者高效率地处理工作事务的同时,也实现了数据信息的整体化,规范化与自动化。 管理员在后台主要管理病例管理、字典管理、公告管理、药单管理、药品管理、药品收藏管理、药品评价管理、药品订单管理、牙医管理、牙医收藏管理、牙医评价管理、牙医挂号管理、用户管理、管理员管理等。 牙医列表页面,此页面提供给管理员的功能有:查看牙医、新增牙医、修改牙医、删除牙医等。公告信息管理页面提供的功能操作有:新增公告,修改公告,删除公告操作。公告类型管理页面显示所有公告类型,在此页面既可以让管理员添加新的公告信息类型,也能对已有的公告类型信息执行编辑更新,失效的公告类型信息也能让管理员快速删除。药品管理页面,此页面提供给管理员的功能有:新增药品,修改药品,删除药品。药品类型管理页面,此页面提供给管理员的功能有:新增药品类型,修改药品类型,删除药品类型。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值