牛客数据库SQL刷题记录 D-6

牛客数据库SQL刷题记录 D-6

1. 题目：查找所有员工自入职以来的薪水涨幅情况

• 题目描述：

查找所有员工自入职以来的薪水涨幅情况，
给出员工编号emp_no以及其对应的薪水涨幅growth，
并按照growth进行升序
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`));

• 输出：

emp_no	growth
10011	0
省略	省略
10010	54496
10004	34003

• 代码：

SELECT se.emp_no, (se.salary-ss.salary) as growth
FROM 
(SELECT e.emp_no, s.salary 
 FROM employees as e 
 INNER JOIN salaries as s
 ON e.emp_no = s.emp_no
 WHERE s.to_date='9999-01-01') as se
INNER JOIN
( SELECT e.emp_no, s.salary
 FROM employees as e
 INNER JOIN salaries as s
 ON e.emp_no =s.emp_no
 WHERE s.from_date=e.hire_date) as ss
ON se.emp_no=ss.emp_no
ORDER BY growth ASC

• 思路：
  emmm还有点复杂

1. 首先薪水的涨幅情况，可以用现在的薪水 - 入职时的薪水表示，记为growth
2. 建立两张表，一张表示现在的薪水，一张表示入职时的薪水。
3. 找出两个时期相同的emp_no（即同一人），连接这两张表，并将growth按升序排列输出

2. 题目：统计各个部门对应员工涨幅的次数总和

• 题目描述：

统计各个部门对应员工涨幅的次数总和，
给出部门编码dept_no、部门名称dept_name以及次数sum
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 `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`));

• 输出：

dept_no	dept_name	sum
d001	Marketing	24
d002	Finance	14
d003	Human Resources	13
d004	Production	24
d005	Development	25
d006	Quality Management	25

• 代码：

SELECT d.dept_no, d.dept_name, COUNT(s.emp_no) as sum
FROM departments as d
INNER JOIN dept_emp as dept
ON d.dept_no=dept.dept_no
INNER JOIN salaries as s
ON dept.emp_no=s.emp_no
GROUP BY d.dept_no

• 思路：

利用COUNT()函数计算在salaries表中emp_no出现的次数记为sum；INNER JOIN连接departments和dept_emp，找出所有员工对应的dept_no; 再连接salaries表，找到对应的薪水。给结果按照dept_no分组

3. 题目：对所有员工的薪水按照salary进行按照1-N的排名

• 题目描述：

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照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`));

• 输出：

emp_no	salary	rank
10005	94692	1
10009	94409	2
10010	94409	2
10001	88958	3
10007	88070	4
10004	74057	5
10002	72527	6
10003	43311	7
10006	43311	7
10011	25828	8

• 代码：

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) as rank 
FROM salaries as s1, salaries as s2
WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01'
AND s1.salary<=s2.salary
GROUP BY s1.emp_no 
ORDER BY rank ASC, s1.emp_no ASC

• 思路：
  这个题需要用两次salaries表，通过对两张相同的表进行比较，找出当前（to_date=‘9999-01-01’）员工薪水的排名情况。
  s1.salary<=s2.salary 是用来找出有几个薪水比当前薪水更大。COUNT()函数用于计算比当前薪水值更大的值个数，因为可能存在相同的薪水，所以用DISTINCT去重。最后用GROUP BY 函数对结果进行分组，ORDER BY进行排序。
  思路参考：牛客 @ciphersaw 的回答

4. 题目：获取所有非manager员工当前的薪水情况

• 题目：

获取所有非manager员工当前的薪水情况，
给出dept_no、emp_no以及salary ，
当前表示to_date='9999-01-01'
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`));
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`));

• 输出：

dept_no	emp_no	salary
d001	10001	88958
d004	10003	43311
d005	10007	88070
d006	10009	95409

• 代码：

SELECT dept.dept_no, dept.emp_no, s.salary
FROM dept_emp as dept
INNER JOIN salaries as s
on dept.emp_no=s.emp_no
AND s.to_date='9999-01-01'
WHERE  dept.emp_no NOT IN 
(
    SELECT m.emp_no FROM dept_manager as m
    WHERE m.to_date='9999-01-01'
)

• 思路：
  连接dept_emp和salaries两个表，找到当前所有部门所有员工的薪水情况；再用NOT IN找出新连接的表中所有不在dept_manager表中的员工薪水情况，即为不是manager员工的薪水。

2019/11/28 出门溜达了一个星期，一周没写，抓紧时间继续写，题目感觉突然难了起来orz