Date: 2019-08-05
1. 查找最晚入职员工的所有信息
题目描述
查找最晚入职员工的所有信息
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`));
输入描述:
无
输出描述:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
采用子查询的方式获取hire_date的最大时间,然后根据这一筛选条件进行筛选即可
SELECT *
FROM employees
WHERE hire_date =
(SELECT MAX(hire_date)
FROM employees)
2. 查找入职员工时间排名倒数第三的员工所有信息
题目描述
查找入职员工时间排名倒数第三的员工所有信息
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`));
输入描述:
无
输出描述:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
排名倒数第三,则采用降序排列,然后选择第三条元素的信息,第三条元素的选择是采用LIMIT x,y ,其中:x是从x+1开始进行获取,共计选择y条元素。
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 2,1;
3. 查找当前薪水详情以及部门编号dept_no
题目描述
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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 `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 | from_date | to_date | dept_no |
---|---|---|---|---|
10002 | 72527 | 2001-08-02 | 9999-01-01 | d001 |
10004 | 74057 | 2001-11-27 | 9999-01-01 | d004 |
10005 | 94692 | 2001-09-09 | 9999-01-01 | d003 |
10006 | 43311 | 2001-08-02 | 9999-01-01 | d002 |
10010 | 94409 | 2001-11-23 | 9999-01-01 | d006 |
注意应该是salaries表在前面,dept_manager表在后面。
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';
4. 查找所有已经分配部门的员工的last_name和first_name
题目描述
查找所有已经分配部门的员工的last_name和first_name
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`));
输入描述:
无
输出描述:
last_name | first_name | dept_no |
---|---|---|
Facello | Georgi | d001 |
省略 | 省略 | 省略 |
Piveteau | Duangkaew | d006 |
主要是考察两个表的连接,需要对dept_表进行dept_no的NOT NULL的筛选。
SELECT e.last_name, e.first_name,d.dept_no
FROM employees AS e
JOIN dept_emp AS d
ON e.emp_no = d.emp_no
WHERE d.dept_no NOT NULL
5. 查找所有员工的last_name和first_name以及对应部门编号dept_no
题目描述
查找所有员工的last_name和first_name以及对应部门编号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`));
输入描述:
无
输出描述:
last_name | first_name | dept_no |
---|---|---|
Facello | Georgi | d001 |
省略 | 省略 | 省略 |
Sluis | Mary | NULL(在sqlite中此处为空,MySQL为NULL) |
因为题意是所有员工,则进行左连接。
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e
LEFT JOIN dept_emp AS d
ON e.emp_no = d.emp_no
6. 查找所有员工入职时候的薪水情况
题目描述
查找所有员工入职时候的薪水情况,给出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`));
输入描述:
无
输出描述:
emp_no | salary |
---|---|
10011 | 25828 |
省略 | 省略 |
10001 | 60117 |
两点:
1)入职时的薪水,则根据s.from_date = e.hire_date 进行筛选, from_date是这部分工资的计算时间,入职薪水,from_date应该从hire_date开始计算。
2) 降序排列:order by e.emp_no DESC
SELECT e.emp_no, s.salary
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
WHERE e.hire_date = s.from_date
ORDER BY e.emp_no DESC
牛客上他人的解析:
此题应注意以下四个知识点:
1、由于测试数据中,salaries.emp_no 不唯一(因为号码为 emp_no 的员工会有多次涨薪的可能,所以在 salaries 中对应的记录不止一条),employees.emp_no 唯一,即 salaries 的数据会多于 employees,因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no,则有限制条件 e.emp_no = s.emp_no
2、根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等,因此有限制条件 e.hire_date = s.from_date
3、根据题意要按照 emp_no 值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC
4、为了代码良好的可读性,运用了 Alias 别名语句,将 employees 简化为 e,salaries 简化为s,即 employees AS e 与 salaries AS s,其中 AS 可以省略
方法一:利用 INNER JOIN 连接两张表
1 2 3 |
|
方法二:直接用逗号并列查询两张表
1 2 3 |
|
7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
题目描述
查找薪水涨幅超过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`));
输入描述:
无
输出描述:
emp_no | t |
---|---|
10001 | 17 |
10004 | 16 |
10009 | 18 |
SELECT s.emp_no AS emp_no, COUNT(s.emp_no) AS t
FROM salaries AS s
GROUP BY s.emp_no
HAVING t > 15;
解释:
此题应注意以下四点:
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意,输出的涨幅次数为t,故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
4、最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可
/** 注意: 严格来说,下一条salary高于本条才算涨幅,但本题只要出现了一条记录就算一次涨幅,salary相同可以理解为涨幅为0,salary变少理解为涨幅为负 **/
8. 找出所有员工当前薪水salary情况
题目描述
找出所有员工当前(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`));
输入描述:
无
输出描述:
salary |
---|
94692 |
94409 |
88958 |
88070 |
74057 |
72527 |
59755 |
43311 |
25828 |
SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC;
9.
题目描述
获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
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`));
输入描述:
无
输出描述:
dept_no | emp_no | salary |
---|---|---|
d001 | 10002 | 72527 |
d004 | 10004 | 74057 |
d003 | 10005 | 94692 |
d002 | 10006 | 43311 |
d006 | 10010 | 94409 |
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_manager AS d
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';
解释:
1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no,并且将salaries用别名s代替,dept_manager用别名d代替
2、根据题意,要获取当前manager的当前salary情况,再加上限制条件d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'即可(因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = '9999-01-01'时是该员工当前的薪水记录)
10. 获取所有非manager的员工emp_no
题目描述
获取所有非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`));
输入描述:
无
输出描述:
emp_no |
---|
10001 |
10003 |
10007 |
10008 |
10009 |
10011 |
SELECT e.emp_no AS emp_no
FROM employees AS e
WHERE e.emp_no NOT IN
(SELECT d.emp_no
FROM dept_manager AS d
)
牛客上其他版本,第三种方法的结果最优:
方法一:使用NOT IN选出在employees但不在dept_manager中的emp_no记录
1 2 |
|
方法二:先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录
1 2 3 |
|
方法三:方法二的简版,使用单层SELECT语句即可
1 2 3 |
|
11. 获取所有员工当前的manager
题目描述
获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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`));
输入描述:
无
输出描述:
emp_no | manager_no |
---|---|
10001 | 10002 |
10003 | 10004 |
10009 | 10010 |
分析:
本题应注意以下三点:
1、用 INNER JOIN 连接两张表,因为要输出自己的经理,得知自己与经理的部门要相同,故有限制条件 de.dept_no = dm.dept_no
2、再用 WHERE 限制当前员工与当前经理的条件,即 dm.to_date 等于 '9999-01-01' 、de.to_date 等于 '9999-01-01' 、 de.emp_no 不等于 dm.emp_no
3、为了增强代码可读性,将 dept_emp 用别名 de 代替,dept_manager 用 dm 代替,最后根据题意将 de.emp_no 用别名 manager_no 代替后输出
SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de
JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
WHERE de.emp_no <> dm.emp_no AND dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01';
12. 获取所有部门中当前员工薪水最高的相关信息
题目描述
获取所有部门中当前员工薪水最高的相关信息,给出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`));
输入描述:
无
输出描述:
dept_no | emp_no | salary |
---|---|---|
d001 | 10001 | 88958 |
d002 | 10006 | 43311 |
d003 | 10005 | 94692 |
d004 | 10004 | 74057 |
d005 | 10007 | 88070 |
d006 | 10009 | 95409 |
SELECT de.dept_no, de.emp_no, MAX(s.salary) AS salary
FROM dept_emp AS de, salaries AS s
WHERE de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
AND de.emp_no = s.emp_no
GROUP BY de.dept_no;
相似实现解释:
1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
2、选取每个员工当前的工资水平,用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制,因为此表中每条最新记录的 to_date 都用 9999-01-01 表示;
3、用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者;
4、将salaries用s代替,dept_emp用d代替,最后将MAX(s.salary)用salary代替后输出。
1 2 3 4 5 |
|
13. 从titles表获取按照title进行分组
题目描述
从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);
输入描述:
无
输出描述:
title | t |
---|---|
Assistant Engineer | 2 |
Engineer | 4 |
省略 | 省略 |
Staff | 3 |
SELECT title, COUNT(title) AS t
FROM titles
GROUP BY title
HAVING t >=2;
14. 从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略。
题目描述
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
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 | t |
---|---|
Assistant Engineer | 2 |
Engineer | 3 |
省略 | 省略 |
Staff | 3 |
去重复的语句是SELECT DISTINCT,则利用此语句先进行筛选。
SELECT title, COUNT(title) AS t
FROM
(SELECT DISTINCT emp_no, title
FROM titles)
GROUP BY title
HAVING t >=2;
15. 查找employees表
题目描述
查找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`));
输入描述:
无
输出描述:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
SELECT *
FROM employees
WHERE emp_no % 2 =1 AND last_name <> 'Mary'
ORDER BY hire_date DESC
16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资
题目描述
统计出当前各个title类型对应的员工当前(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 |
---|---|
Engineer | 94409.0 |
Senior Engineer | 69009.2 |
Senior Staff | 91381.0 |
Staff | 72527.0 |
SELECT t.title AS title, AVG(s.salary) AS avg
FROM titles AS t, salaries AS s
WHERE t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
AND t.emp_no = s.emp_no
GROUP BY t.title;
17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
题目描述
获取当前(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`));
输入描述:
无
输出描述:
emp_no | salary |
---|---|
10009 | 94409 |
SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC
LIMIT 1,1;
注意:1) 薪水第二多的筛选; 2) 当前时间的筛选; 3)降序排序
18. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary,不准使用order by
题目描述
查找当前薪水(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`));
输入描述:
无
输出描述:
emp_no | salary | last_name | first_name |
---|---|---|---|
10009 | 94409 | Peac | Sumant |
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e
JOIN salaries AS s
ON s.emp_no = e.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN
(SELECT MAX(salary)
FROM salaries
WHERE to_date = '9999-01-01')
# 下面不通过
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e
JOIN salaries AS s
ON s.emp_no = e.emp_no
WHERE s.to_date = '9999-01-01'
WHERE e.to_date = '9999-01-01'
AND s.salary NOT IN
(SELECT MAX(salary)
FROM salaries
WHERE to_date = '9999-01-01')
主要思想为多层SELECT嵌套与MAX()函数结合
1、先利用MAX()函数找出salaries中当前薪水最高者,即SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'
2、再利用INNER JOIN连接employees与salaries表,限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
3、在以上限制条件下找薪水最高者,即为所有员工薪水的次高者
19. 查找所有员工的last_name和first_name以及对应的dept_name
题目描述
查找所有员工的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`));
输入描述:
无
输出描述:
last_name | first_name | dept_name |
---|---|---|
Facello | Georgi | Marketing |
省略 | 省略 | 省略 |
Sluis | Mary | NULL |
SELECT e.last_name, e.first_name, d.dept_name
FROM employees AS e
LEFT JOIN dept_emp AS de
ON e.emp_no = de.emp_no
LEFT JOIN departments AS d
ON de.dept_no = d.dept_no
两次LEFT JOIN,主要是考虑到题目中提到的需要考虑目前没有分配部门的员工,同时有的部门目前可能没有名称dept_name。
20. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
题目描述
查找员工编号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`));
输入描述:
无
输出描述:
growth |
---|
28841 |
SELECT (MAX(salary)-MIN(salary)) AS growth
FROM salaries
WHERE emp_no = '10001'
** 筛选直接where; 对于涨幅的计算直接根据最大值-最小值就是涨幅。这种方法不够严谨。
本题严谨的思路如下:
1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录
2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅,最后用别名growth代替
1 2 3 4 |
|
21. 查找所有员工自入职以来的薪水涨幅情况
题目描述
查找所有员工自入职以来的薪水涨幅情况,给出员工编号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 | 34 |
本题思路是先分别用两次LEFT JOIN左连接employees与salaries,建立两张表,分别存放员工当前工资(sCurrent)与员工入职时的工资(sStart),再用INNER JOIN连接sCurrent与sStart,最后限定在同一员工下用当前工资减去入职工资。
方法一:内层用LEFT JOIN,外层用INNER JOIN(内层也可以改用 INNER JOIN)
1 2 3 4 5 |
|
方法二:内外都层用FROM并列查询
1 2 3 4 5 |
|
22. 统计各个部门对应员工涨幅的次数总和
题目描述
统计各个部门对应员工涨幅的次数总和,给出部门编码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 |
本题关键是要将 每个部门分组,并分别统计工资记录总数,思路如下:
1、用INNER JOIN连接dept_emp表和salaries表,并以dept_emp.no分组,统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表,限制条件为两表的dept_no相等,找到dept_no与dept_name的对应关系,最后依次输出dept_no、dept_name、sum
SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no)
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no
GROUP BY de.dept_no
# 另一种
SELECT d.dept_no ,dept.dept_name,COUNT(salary)
FROM salaries s ,dept_emp d,departments dept
WHERE s.emp_no = d.emp_no AND d.dept_no=dept.dept_no
GROUP BY dept.dept_no
23. 对所有员工的薪水按照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 s1, salaries s2
WHERE s1.salary <= s2.salary AND s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01'
GROUP BY s1.emp_no
ORDER BY rank;
其他牛客上的解析:
本题的主要思想是复用salaries表进行比较排名,具体思路如下:
1、从两张相同的salaries表(分别为s1与s2)进行对比分析,先将两表限定条件设为to_date = '9999-01-01',挑选出当前所有员工的薪水情况。
2、本题的精髓在于 s1.salary <= s2.salary,意思是在输出s1.salary的情况下,有多少个s2.salary大于等于s1.salary,比如当s1.salary=94409时,有3个s2.salary(分别为94692,94409,94409)大于等于它,但由于94409重复,利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
3、千万不要忘了GROUP BY s1.emp_no,否则输出的记录只有一条(可能是第一条或者最后一条,根据不同的数据库而定),因为用了合计函数COUNT()
4、最后先以 s1.salary 逆序排列,再以 s1.emp_no 顺序排列输出结果
1 2 3 4 5 |
|
最后在支持ROW_NUMBER、RANK、DENSE_RANK等函数的SQL Server数据库中,有以下参考代码,可惜在本题的SQLite数据库中不支持。
1 2 |
|
24. 获取所有非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 de.dept_no, de.emp_no, s.salary
FROM dept_emp de, salaries s
WHERE de.emp_no = s.emp_no AND s.to_date = '9999-01-01' AND de.emp_no NOT IN
(SELECT dm.emp_no
FROM dept_manager AS dm
WHERE dm.to_date = '9999-01-01')
牛客上其他思想方式:
1、先用INNER JOIN连接employees和salaries,找出当前所有员工的工资情况
2、再用INNER JOIN连接dept_emp表,找到所有员工所在的部门
3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')选出当前所有非manager员工,再依次输出dept_no、emp_no、salary
1 2 3 4 |
|
此外,还能作如下简化,不连接employees表也能完成:
1 2 3 |
|
25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `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`));
输入描述:
无
输出描述:
emp_no | manager_no | emp_salary | manager_salary |
---|---|---|---|
10001 | 10002 | 88958 | 72527 |
10009 | 10010 | 95409 | 94409 |
SELECT sem.emp_no emp_no,sdm.emp_no manager_no, sem.salary emp_salary, sdm.salary manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries AS s JOIN dept_emp AS de ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01') AS sem
JOIN (SELECT s.salary, s.emp_no, dm.dept_no FROM salaries AS s JOIN dept_manager AS dm ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01') AS sdm
ON sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
解析思路:
本题主要思想是创建两张表(一张记录当前所有员工的工资,另一张只记录部门经理的工资)进行比较,具体思路如下:
1、先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager,建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
26. 汇总各个部门当前员工的title类型的分配数目
题目描述
汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
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 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);
输入描述:
无
输出描述:
dept_no | dept_name | title | count |
---|---|---|---|
d001 | Marketing | Senior Engineer | 1 |
d001 | Marketing | Staff | 1 |
d002 | Finance | Senior Engineer | 1 |
d003 | Human Resources | Senior Staff | 1 |
d004 | Production | Senior Engineer | 2 |
d005 | Development | Senior Staff | 1 |
d006 | Quality Management | Engineer | 2 |
d006 | Quality Management | Senior Engineer | 1 |
SELECT dep.dept_no, dep.dept_name, A.title, COUNT(A.title) AS count
FROM departments AS dep
JOIN (SELECT t.emp_no, t.title, de.dept_no
FROM titles t
JOIN dept_emp de
ON t.emp_no = de.emp_no AND t.to_date = '9999-01-01' AND de.to_date = '9999-01-01'
) AS A
ON dep.dept_no = A.dept_no
GROUP BY dep.dept_no, A.title;
在这里,并不需要将聚合函数count函数前的所有字段都加入到GROUP BY 中,合适的进行筛选。
另一种实现思路解析:
本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组,具体思路如下:
1、先用 INNER JOIN 连接 dept_emp 与 salaries,根据测试数据添加限定条件 de.to_date = '9999-01-01' AND t.to_date = '9999-01-01',即当前员工的当前头衔
2、再用 INNER JOIN 连接departments,限定条件为 de.dept_no = dp.dept_no,即部门编号相同
3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组,用 COUNT(t.title) 统计相同部门下相同头衔的员工个数
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title
27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no
题目描述
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
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 | from_date | salary_growth |
---|---|---|
10003 | 1995-12-03 | 24178 |
10008 | 1998-03-11 | 20843 |
10008 | 2000-03-10 | 5997 |
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND s2.salary - s1.salary > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC;
另一种考虑的更全面 但实现更低一点。
本题的难点在于如何理解 每年薪水涨幅,以及复用salaries表求出每年薪水涨幅超过5000的员工,具体思路如下:
1、假设s1是涨薪水前的表,s2是涨薪水后的表,因为每个员工涨薪水的时间不全固定,有可能一年涨两次,有可能两年涨一次,所以每年薪水的涨幅,应该理解为两条薪水记录的from_date相同或to_date相同。
/** 如果只限定to_date相同,则将第三条原始测试数据的52668改成62668时,就会少一条【62668-48584=14084】的记录
INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11');
INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10');
INSERT INTO salaries VALUES(10008, 62668 ,'2000-03-10','2000-07-31'); **/
2、找到s1与s2符合要求的记录后,用s2的薪水减去s1的薪水,用salary_growth表示,加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000,即同一员工每年涨幅超过5000的记录
3、最后依次输出emp_no、from_date、salary_growth,并以salary_growth逆序排列
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC
28.
题目描述
film表
字段 | 说明 |
film_id | 电影id |
title | 电影名称 |
description | 电影描述信息 |
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段 | 说明 |
category_id | 电影分类id |
name | 电影分类名称 |
last_update | 电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段 | 说明 |
film_id | 电影id |
category_id | 电影分类id |
last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
SELECT c.name, COUNT(fc.film_id) FROM
(select category_id, COUNT(film_id) AS category_num FROM
film_category GROUP BY category_id HAVING count(film_id)>=5) AS cc,
film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
以上思路分析:
1、找到对应电影数量>=5的所有分类,建立成虚表cc:(select category_id, count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc
2、设定限制条件 f.description like '%robot%'
3、在表cc、f、fc、c中查找包括robot的电影对应的分类名称和对应的电影数目。
29. 使用join查询方式找出没有分类的电影id以及名称
题目描述
film表
字段 | 说明 |
film_id | 电影id |
title | 电影名称 |
description | 电影描述信息 |
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段 | 说明 |
category_id | 电影分类id |
name | 电影分类名称 |
last_update | 电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段 | 说明 |
film_id | 电影id |
category_id | 电影分类id |
last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
使用join查询方式找出没有分类的电影id以及名称
SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id = fc.film_id
WHERE fc.category_id IS NULL
上面思路分析:
解题思路是运用 LEFT JOIN 连接两表,用 IS NULL 语句限定条件:
1、用 LEFT JOIN 连接 film 和 film_category,限定条件为 f.film_id = fc.film_id,即连接电影 id 和电影分类 id,如果电影没有分类,则电影分类 id 显示 null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
/* 注意:最后一句若写成 ON f.film_id = fc.film_id AND fc.category_id IS NULL,则意义变成左连接两表 film_id 相同的记录,且 film_category 原表中的 fc.category 的值为 null。显然,原表中的 fc.category 的值恒不为 null,因此(f.film_id = fc.film_id AND fc.category_id IS NULL)恒为 FALSE,左连接后则只会显示 film 表的数据,而 film_category 表的数据全显示为 null */