牛客数据库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还有点复杂
- 首先薪水的涨幅情况,可以用现在的薪水 - 入职时的薪水表示,记为growth
- 建立两张表,一张表示现在的薪水,一张表示入职时的薪水。
- 找出两个时期相同的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