牛客网-在线编程-sql实战76道-----11-20 (附链接-Day2-学习中)

牛客sql实战11-20

牛客sql实战第11题

获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL, -- '所有的员工编号'
`dept_no` char(4) NOT NULL, -- '部门编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '经理编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

这道题没啥说得,直接 join ,然后把该带的条件带上

select e.emp_no, m.emp_no manager_no from dept_emp e
join dept_manager 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';

牛客sql实战第12题

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

这道题就是求每个部门的员工的最高薪水,此题讨论最多的就是最大薪水和emp_no对应的问题,以及 group by 前面的select 中只能出现 分组字段和聚合函数 ,关联子查询

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

如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值),则用INNER JOIN和GROUP BY和MAX即可解决:

SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON 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;

牛客sql实战第13题

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

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

这道题没什么说的,直接写

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

牛客sql实战第14题

从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算,title对应的数目t不增加)。

CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

上一道题的变种,去重即可

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

牛客sql实战第15题

查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

主要是奇数的表达方式:取余(oracle中不通用),mod函数,位运算
还有一个是关于不等号:<> 和 !=(oracle中不通用)

select * from employees where emp_no % 2 = 1 and last_name <> 'Mary' order by hire_date desc
select * from employees where emp_no & 1 = 1 and last_name <> 'Mary' order by hire_date desc
select * from employees where mod(emp_no,2) = 1 and last_name <> 'Mary' order by hire_date desc

牛客sql实战第16题

统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

要求每个 title 的平均工资,avg + group by

select t.title, avg(s.salary) avg from salaries s
join titles t on s.emp_no = t.emp_no 
and t.to_date = '9999-01-01'
and s.to_date = '9999-01-01' 
group by title

牛客sql实战第17题

获取当前(to_date=‘9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

倒序 + limit

select emp_no, salary 
from salaries 
where to_date='9999-01-01'
order by salary desc limit 1,1

以上写法没有考虑工资相同的情况, 先用group by去重得到第二大薪资,再查即可

select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1
) 
and to_date = '9999-01-01'

或者用 distinct

select emp_no, salary from salaries
where to_date = '9999-01-01' 
and salary = 
(select distinct salary from salaries order by salary desc limit 1,1)

牛客sql实战第18题

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,你可以不使用order by完成吗

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

把第二大薪资查出来,再 join ,由于不能用 order by , 所以改为 max,具体看 sql语句

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e join salaries s 
on e.emp_no=s.emp_no 
and s.to_date='9999-01-01'
and s.salary = 
    (select max(salary) from salaries 
     where salary < (select max(salary) from salaries where to_date='9999-01-01')
    and to_date='9999-01-01')

牛客sql实战第19题

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

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

left join 直接查

select e.last_name, e.first_name,d.dept_name from employees e
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no

牛客sql实战第20题

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

入职以来最大减去最小即可

select (
(select salary from salaries where emp_no=10001 order by from_date desc limit 1)-
(select salary  from salaries  where emp_no=10001 order by from_date limit 1)
) as growth;

或者,只针对没有降薪的情况

SELECT (MAX(salary)-MIN(salary)) AS growth 
FROM salaries WHERE emp_no = '10001'
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页