2021-04-14

自连接查询

*创建部门表、员工表

-- 部门表
create table dept2(
	deptno int primary key auto_increment, -- 部门编号
	dname varchar(14) ,	  -- 部门名字
	loc varchar(13)   -- 地址
) ;
-- 员工表
create table emp2(
	empno int primary key auto_increment,-- 员工编号
	ename varchar(10), -- 员工姓名										-
	job varchar(9),	-- 岗位
	mgr int,	 -- 直接领导编号
	hiredate date, -- 雇佣日期，入职日期
	sal int, -- 薪水
	comm int,  -- 提成
	deptno int not null, -- 部门编号
	foreign key (deptno) references dept2(deptno)
);

添加测试数据

-- 部门表
insert into dept2 values(10,'财务部','北京');
insert into dept2 values(20,'研发部','上海');
insert into dept2 values(30,'销售部','广州');
insert into dept2 values(40,'行政部','深圳');
-- 员工表
insert into emp2 values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp2 values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp2 values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp2 values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp2 values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp2 values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp2 values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp2 values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp2 values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp2 values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp2 values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp2 values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp2 values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp2 values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);

1. 列出所有员工的姓名及其直接上级的姓名

select 
	a.ename, b.ename
	from emp2 as a, -- a：员工
	emp2 as b  -- b：上级
	where a.mgr = b.empno;

2. 列出受雇日期早于其直接上级的所有员工

select
	a.ename,b.ename 
	from emp2 as a,
	emp2 as b
	where a.mgr = b.empno and a.hiredate < b.hiredate;