23_MySQL单行和多行子查询语法规则（重点）

-- 如何用子查询查找FORD和MARTIN两个人的同事？

1 WHERE子查询

SELECT ename 
FROM t_emp
WHERE deptno IN (SELECT deptno FROM t_emp WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD","MARTIN");

2 FROM子查询

SELECT ename 
FROM t_emp e JOIN (SELECT deptno FROM t_emp WHERE ename IN ("FORD","MARTIN")) d ON e.deptno=d.deptno
WHERE e.ename NOT IN ("FORD","MARTIN");

-- 查询比FORD和MARTIN底薪都高的员工信息？

1.找出FORD和MARTIN中底薪，并保留底薪较高的那个
2.表连接，找出高出较高底薪的员工

SELECT empno,ename
FROM t_emp e1
JOIN (SELECT MAX(sal) AS sal FROM t_emp WHERE ename IN ("FORD","MARTIN")) e2
ON e1.sal>=e2.sal
WHERE e1.ename NOT IN ("FORD","MARTIN");