题目:查找所有员工入职时候的薪水情况,给出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
));
分析:题目的最主要目的就是查出员工入职的时候的薪水情况,问题在哪呢,问题在于一个员工会有多次涨薪的情况。我们画出这两张表,比较下
employees表
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
001 | 9999-09-09 | big | dog | 1 | 1998-01-10 |
…… | …… | …… | …… | …… | …… |
我们思考下这张表的情况,这张表的emp_no应该是没有重复的,因为上面这些信息都不会重复,这几个字段没有重复的意义。
salaries表
emp_no | salary | from_date | to_date |
---|---|---|---|
001 | 2000 | 1998-01-10 | 1998-12-31 |
001 | 5000 | 1998-12-31 | 1999-07-31 |
002 | 1000 | 1996-12-31 | 1997-07-31 |
…… | …… | …… | …… |
这张salaries会展现出涨薪的情况,就是这个人在from_date到to_date这段时间的salary是多少,所以如果我们需要找出这个人的入职时间,其实就是找出他的最小入职时间,sql语句应该是
select min(from_date),salary,emp_no from salaries group by emp_no
这样我们会形成一个表,这个表显示的就是salaries表的每个人的入职的时候的工资。不包含他涨薪的情况。我们拿到这样一张表后,使用employees表进行左连接就行了。注意,在这个新表中是没有hire_date的,第一次入职的from_date就是hire_date。
答案如下:
select e.emp_no,new_s.salary from employees e
left join (select min(from_date),salary,emp_no from salaries group by emp_no) new_s on
e.emp_no = new_s.emp_no order by e.emp_no desc;
结果为:
后记:这个题提交了27次才通过,我自己都无语,给我的感觉就是一定要画出表,然后对表进行分析,不然空想很容易想错。