叨叨两句
- ~
SQL习题017
1
题目描述
获取所有非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
));
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
SELECT de.dept_no, s.emp_no, s.salary
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = '9999-01-01')
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')
此外,还能作如下简化,不连接employees表也能完成:
SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。