23_MySQL单行和多行子查询语法规则(重点)

23_MySQL单行和多行子查询语法规则(重点)

 

本节涉及SQL语句:

-- 如何用子查询查找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");

 

23_MySQL单行和多行子查询语法规则(重点)

23_MySQL单行和多行子查询语法规则(重点)

 

 23_MySQL单行和多行子查询语法规则(重点)

 

 

23_MySQL单行和多行子查询语法规则(重点)

 

23_MySQL单行和多行子查询语法规则(重点)

上一篇:MySQL跨库数据表映射方案


下一篇:PHP新的垃圾回收机制:Zend GC详解