MySQL --- 子查询 - 表子查询

表子查询

子查询返回的是多行多列的数据，就是一个表格

必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较

• 在emp表中，得到与10号部门任何一个员工入职日期和领导相同的员工信息(用在where子句中)

-- 查询10号部门所有的入职年份和领导
SELECT DATE_FORMAT(hiredate,'%Y'),mgr FROM emp WHERE deptno=10;
-- 查询其他员工
SELECT * FROM emp WHERE (DATE_FORMAT(hiredate,'%Y'),mgr) IN
(SELECT DATE_FORMAT(hiredate,'%Y'),mgr FROM emp WHERE deptno=10);

属于集合的操作，需要用IN

 

• 查询出每个部门的编号、名称、位置、部门人数、平均工资（用在from子句中）

#以前学的多表联合查询
SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),ROUND(AVG(sal),2) FROM
dept d LEFT JOIN emp e
ON e.deptno=d.deptno
GROUP BY d.deptno,d.dname,d.loc;
​
#用子查询联合查询
SELECT dept.deptno,dept.dname,dept.loc,d.count,d.avgsal FROM dept LEFT JOIN 
(SELECT deptno,COUNT(*) count,AVG(sal) avgsal  FROM emp GROUP BY deptno) d
ON dept.deptno=d.deptno;

在最前加上部门编号，我们要做的其实是把第一个表和第二个表进行结合，用部门编号作为关联字段，我们只需要把第二个查询出来的表当作临时表，对于子查询临时表都需要取一个别名，否则的话是无法操作的，最后加上条件，以部门编号进行等值连接

显示部门人数与平均工资，需要对它进行取别名处理

用 ROUND 函数对平均工资四舍五入，保留两位小数

• 查询出所有在'SALES'部门工作的员工编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。(where和from子句同时使用)

#1
SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno  
FROM emp e JOIN 
(SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno) td
ON e.deptno=td.deptno AND e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');
​
#2
SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno  
FROM emp e JOIN 
(SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno
HAVING deptno=(SELECT deptno FROM dept WHERE dname='SALES')) td
ON e.deptno=td.deptno;

• 查询出比 'ALLEN' 或 'CLACRK' 薪资多的所有员工的编号、姓名、基本工资、部门名称、领导姓名、部门人数。

#隐式方式
SELECT e.empno,e.ename,e.sal,d.dname,me.ename 领导,temp.count FROM emp e,dept d,emp me,
(SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp
WHERE e.deptno=d.deptno AND e.mgr=me.empno AND temp.deptno=e.deptno
AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK');

#显示方式
SELECT e.empno,e.ename,e.sal,d.dname,me.ename 领导,temp.count 
FROM emp e 
JOIN dept d ON e.deptno=d.deptno
LEFT JOIN emp me ON e.mgr=me.empno
JOIN (SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp ON temp.deptno=e.deptno
AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK');

• 列出公司各个部门的经理(假设每个部门只有一个经理，job为 'MANAGER' )的姓名、薪资、部门名称、部门人数、部门平均工资。

#隐式方式
SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal
FROM emp e,dept d,(SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp
 WHERE job='MANAGER' AND e.deptno=d.deptno AND temp.deptno=e.deptno;
 
 #显示方式
SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal
FROM emp e 
JOIN dept d ON e.deptno=d.deptno 
JOIN (SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno
 AND job='MANAGER';

• 查询出所有薪资高于公司平均薪资的员工编号、姓名、基本工资、职位、雇佣日期、所在部门名称、部门位置、上级领导姓名、工资等级、部门人数、平均工资、平均服务年限。

#隐式方式
SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 领导,s.grade,temp.count,temp.avgsal,temp.avgyear
FROM emp e,dept d,emp me,salgrade s,(SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp 
WHERE e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp)
AND e.mgr=me.empno
AND e.sal BETWEEN s.losal AND s.hisal
AND temp.deptno=e.deptno;
​
#显示方式
SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 领导,s.grade,temp.count,temp.avgsal,temp.avgyear
FROM emp e 
JOIN dept d ON e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp)
LEFT JOIN emp me ON e.mgr=me.empno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
JOIN (SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno;