【363天】我爱刷题系列122（2018.02.03）

叨叨两句

1. ~

SQL习题017

1

题目描述
获取所有非manager员工当前的薪水情况&＃xff0c;给出dept_no、emp_no以及salary &＃xff0c;当前表示to_date&＃61;&＃39;9999-01-01&＃39;
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&＃xff0c;找出当前所有员工的工资情况
2、再用INNER JOIN连接dept_emp表&＃xff0c;找到所有员工所在的部门
3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date &＃61; &＃39;9999-01-01&＃39;)选出当前所有非manager员工&＃xff0c;再依次输出dept_no、emp_no、salarySELECT de.dept_no, s.emp_no, s.salary
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no &＃61; e.emp_no AND s.to_date &＃61; &＃39;9999-01-01&＃39;)
INNER JOIN dept_emp AS de ON e.emp_no &＃61; de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date &＃61; &＃39;9999-01-01&＃39;)
此外&＃xff0c;还能作如下简化&＃xff0c;不连接employees表也能完成&＃xff1a;SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no &＃61; de.emp_no AND s.to_date &＃61; &＃39;9999-01-01&＃39;
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date &＃61; &＃39;9999-01-01&＃39;)