子查询-多行多列

 

# 4.查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT * from emp WHERE (job,sal) in (SELECT job,sal FROM emp WHERE ename = 'MARTIN');

# 5."有2个以上直接下属" 的员工信息【查找经理信息，并且这个经理有2个以上下属】
#并显示下属个数
select ename,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr)>2;
SELECT * FROM emp t1,(select mgr,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr) >= 2) t2 
where t1.empno = t2.mgr; -- t2.mgr上级编号和t1的empno员工编号是对应关系

-- 6.查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
-- 多表多列，用于表
SELECT d.depno,d.name,d.location FROM depart d;
SELECT e.ename '员工名称',e.sal '员工工资',d.name '部门名称',d.location '部门地址'  
FROM emp e,(SELECT depno,name,location FROM depart) d
where e.depno = d.depno AND e.empno = 7788;

-- 求各个部门薪水最高的员工所有信息
-- 求每个部门最高的工资
SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno;

SELECT t1.* FROM emp t1,(SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno) t2
WHERE t1.sal = t2.maxsal;