牛客SQL（1-11）—2020/8/29

最新推荐文章于 2024-07-24 16:34:55 发布

DA_Joyce

最新推荐文章于 2024-07-24 16:34:55 发布

阅读量156

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/dataA_Jia/article/details/108292917

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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

创建employees表

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`));

1.1

按时间降序排列，选择第一条数据

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

LIMIT m,n : 表示从第m+1条开始，取n条数据；

LIMIT n ：表示从第0条开始，取n条数据，是limit(0,n)的缩写。

本题用 DESC LIMIT 0,1 表示从第(0+1)条数据开始，取一条数据，即取出最晚入职员工。

1.2

如果需要考虑最晚那一天有多个人入职的话，那么除了对hire_date进行逆序排序外，还需要对emp_no进行逆序排序，才能得到那个最晚的入职者。

hire_date要写在emp_no前面 :

SELECT * FROM employees ORDER BY hire_date,emp_no DESC LIMIT 1;

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面。

1.3

如果我们想找出最后一天入职的所有人的信息, 先用子查询语句找出最晚入职日期

select * from employees 
where hire_date in (select max(hire_date) from employees);

此处用 in 或者 = 都可以

select *  from employees 
where hire_date = (select max(hire_date) from employees);

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

2.1

先找到时间排名倒数第三是哪个时间, 需要用 distinct 去重

select distinct hire_date from employees oder by hire_date desc limit 2,1;

2.2

外层用where筛选在该日期入职的所有员工

select * from employees 
where hire_date = (
    select distinct hire_date from employees order by hire_date desc limit 2,1)

3. 查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no

注意：请以salaries表为主表进行查询，输出结果以salaries.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`));

这里用了联合主键。同一个人可能有多行薪资记录。

举个例子 :
VALUES (‘1’, 100, ‘2018-03-01’, ‘2019-03-01’) #1号员工的曾经薪资是100
VALUES (‘1’, 300, ‘2019-03-02’, ‘9999-01-01’) #1号员工的当前薪资是300

各个部门领导的信息表

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, 
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

这个表也用了联合主建，不仅仅是要知道谁是领导，要指明是哪个部门的领导。

举个例子 :
VALUES (‘sale’, 2, ‘2011-03-01’) #2号销售部曾经的领导
VALUES (‘sale’, 3, ‘9999-01-01’) #3号是销售部现在的领导
VALUES（‘PR’, 2, ‘9999-01-01’) # 2号是公关部的现任领导

3.1

把员工表和部门领导表连接起来，连接条件是员工号，可以筛选出所有部门领导。（但里面也匹配了这些领导曾经的薪资和曾经的任职部门!!!）

INNER JOIN 取交集，没有主次表之分，只有LEFT/RIGHT OUTER JOIN有。

按题目要求筛选当前领导的当前薪水，即 to_date=‘9999-01-01’。
一定要两个表格的时间都限制成规定时间（9999-01-01）, 因为要过滤掉往年发的薪水和已经离职的部门领导。

select  s.*, d.dept_no
from salaries as s 
join dept_manager as d 
on s.emp_no=d.emp_no
where s.to_date = '9999-01-01' and    d.to_date='9999-01-01'
order by s.emp_no asc;

方法二：

SELECT s.*, d.dept_no 
FROM salaries s ,  dept_manager d
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
AND s.emp_no = d.emp_no;

3.2

本题的难点在于发现两个表都有重复数据，两个表的to_date都要做限制。

4. 匹配员工和部门

部门表

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`));

4.1

查找所有已经分配部门的员工的last_name和first_name以及dept_no.

employees表中有所有员工的emp_no ,而dept_emp表中只有分配了部门的员工，我们用内连接取交集即可。

SELECT E.last_name, E.first_name, D.dept_no from employees E 
join  dept_emp D on D.emp_no = E.emp_no;

4.2

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

这道题和上道的区别在于，如果一个员工没有dept_no，那么也要把它查出来，并且dept_no设置为NULL。因此需要用左连接。

SELECT E.last_name, E.first_name, D.dept_no 
from employees E left join dept_emp D
on D.emp_no=E.emp_no;

5.查找所有员工入职时候的薪水情况

要求：给出emp_no以及salary，并按照emp_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`));

薪资表
(请注意，一个员工可能有多次涨薪的情况)

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`));

答案 1: 与employee表连接，匹配员工号和入职日期

select E.emp_no , S.salary
from employees E join salaries S 
ON E.emp_no=S.emp_no and E.hire_date=S.from_date 
order by E.emp_no desc;

答案2：只用salaries表也能找到入职日期

select emp_no,salary from salaries
group by emp_no having min(from_date)
order by emp_no DESC;

答案3：这个不严谨的答案假设刚入职的时候工资最低

select emp_no,min(salary) from salaries
group by emp_no 
order by emp_no DESC;

6.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

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 emp_no,count(from_date) 
from salaries
group by emp_no
having count(from_date)>15;

7.找出所有员工当前(to_date=‘9999-01-01’)薪水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`));

思路：既要筛选 ‘当前员工’ ，还要给选出来的薪水去重，最后逆序。

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

9.获取所有部门当前manager的当前薪水情况

获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

部门经理表

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`));

薪资表

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`));

答案：

1、先 JOIN 两张表，限制条件是两张表的emp_no相同

2、但是同一个部门的经理会发生变化，同一个经理的薪资也会变化，要确保是当前经理的当前薪资，再加上限制条件d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’

select D.dept_no, D.emp_no, S.salary
from dept_manager D 
join salaries S on D.emp_no=S.emp_no
where D.to_date='9999-01-01' and S.to_date='9999-01-01';

10. 获取所有非manager的员工emp_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`));

员工表

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`));

错误写法：

SELECT emp_no NOT IN (SELECT emp_no FROM dept_manager) FROM employees;

正确写法：

SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);

方法2 ：

EXPECT 集合差运算
UNION 集合并运算
INTERSECT 集合交运算

SELECT employees.emp_no FROM employees
EXCEPT
SELECT dept_manager.emp_no FROM dept_manager;

方法3：

SELECT employees.emp_no FROM employees 
LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL;

11. 获取所有员工当前manager

获取所有员工当前的(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`));

解题思路：

员工当前在哪个部门
每个部门的当前经理
匹配员工和经理
删除员工就是本部门经理的情况

select a.emp_no,b.emp_no
from dept_emp a, dept_manager b
where a.dept_no=b.dept_no
and a.emp_no!=b.emp_no
and b.to_date='9999-01-01' and a.to_date='9999-01-01';

方法2：

SELECT e.emp_no, m.emp_no AS manager_no 
FROM dept_emp e
JOIN dept_manager d
ON de.dept_no = dm.dept_no 
WHERE m.to_date = '9999-01-01' 
AND e.to_date = '9999-01-01' 
AND e.emp_no <> m.emp_no;

DA_Joyce

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
牛客SQL（1-11）—2020/8/29

1.查找最晚入职员工的所有信息创建employees表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`
复制链接

扫一扫