题目描述
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received,没有分配奖金的员工不显示对应的bonus类型btype和received
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 `emp_bonus`(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);
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`));
返回的结果格式如下:
e.emp_no | dept_no | btype | received |
---|---|---|---|
10001 | d001 | 1 | 2010-01-01 |
10002 | d001 | 2 | 2010-10-01 |
10003 | d004 | 3 | 2011-12-03 |
10004 | d004 | 1 | 2010-01-01 |
10005 | d003 | ||
10006 | d002 | ||
10007 | d005 | ||
10008 | d005 | ||
10009 | d006 | ||
10010 | d005 | ||
10010 | d006 |
解答
先将 employees与dept_emp 用 inner join 连接,挑选出分配了部门的员工,再用 left join连接 emp_bonus。
select ee.emp_no,ee.dept_no,eb.btype,eb.received
from (employees e inner join dept_emp de on e.emp_no=de.emp_no) ee
left join emp_bonus eb on ee.emp_no=eb.emp_no