【363天】我爱刷题系列122(2018.02.03)
文章目录
叨叨两句
- ~
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')
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论