创建员工表:
create table if not exists emp(
emp_id int primary key auto_increment,emp_name varchar,salary double,dept_id int comment '部门',manager_id int comment '上级部门'
);
插入数据:
insert into emp(emp_name,salary,dept_id,manger_id)values("tom",15000.0,1,null),
("孙岩",5000.0,5,null),
("曹广司",7000.0,3,null),
("王强",6000.0,2,null),
("周杰",15000.0,2,null),
("李华",65000.0,1,null),
("张建安",8000.0,1,null)
1、查询部门与张建国的部门相同,工资大于孙岩工资的员工信息。
select * from emp where depe_id = (
select dept_id from emp where emp_name ="张建国"
)
and salary > (
select salary from emp where emp_name = "孙岩"
);
2、查询孙姓员工所在部门的员工信息
select * from emp
where dept_id in(
select dept_id from emp where emp_name like '孙%'
);