WHERE deptno IN(
SELECT b.deptno FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno) b
WHERE
b.count IN (
SELECT MAX(a.count)FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno ) a));
有两种方法：
方法一：利用分页查询：
-- 1、查询各部门的平均薪水,并进行降序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、分页查询，取出第一行数据，即最高的平均薪水部门编号
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、经上面数据当做一张表，只显示部门编号
SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1;
方法二：
SELECT deptno FROM dept
WHERE deptno IN(
SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
WHERE
b.avgsal IN(
SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
有两种方法：
方法一：利用分页
-- 1、查询各部门的平均薪水,并进行降序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、分页查询，取出第一行数据，即最高的平均薪水部门编号
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、将上面的查询结果当做一张表，联合部门表，查询最终结果
SELECT dname FROM dept d WHERE d.deptno = (
SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1);
方法二：
SELECT dname FROM dept
WHERE
deptno IN(
SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
WHERE
b.avgsal IN(
SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
有两种方法：
方法一：利用分页
-- 1、查询各部门的平均薪水,并进行升序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal ;
-- 2、分页查询，取出第一行数据，即最低的平均薪水部门编号
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal LIMIT 1;
-- 3、将上面的查询结果当做一张表，联合部门表，查询最终结果
SELECT dname FROM dept d WHERE d.deptno = (
SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1);
方法二：
SELECT dname FROM dept
WHERE
deptno IN(
SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
WHERE
b.avgsal IN(
SELECT MIN(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
SELECT d.dname FROM dept d
WHERE
d.deptno IN
(SELECT a.deptno FROM (SELECT e.deptno FROM emp e,salgrade s
WHERE (e.sal BETWEEN s.losal AND s.hisal)GROUP BY e.deptno ORDER BY avg(s.grade)) a) LIMIT 1;
方法一：
-- 1、查询部门经理的薪水及其所属的部门编号
SELECT deptno,job,MIN(sal) FROM emp WHERE job = 'manager' GROUP BY deptno;
-- 2、分页查询，升序排列，只显示第一行数据，即工资最低的部门经理
SELECT deptno FROM emp WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 ;
-- 3、将上面结果当做一张表，联合部门表
SELECT dname FROM dept d WHERE d.deptno = (
SELECT deptno FROM emp WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 );
方法二：
select dname from dept d where d.deptno IN
(select deptno from emp where job='MANAGER' group by deptno order by min(sal));
-- i) 比普通员工的最高薪水还要高的经理人名称:
-- 1、查询普通员工的最高薪水
SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president');
-- 2、查询
SELECT ename,sal FROM emp
WHERE sal >(SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president'))
AND job = 'manager' OR job = 'president';
五：嵌套子查询
-- a) 查询所有【员工工资都大于1000】的部门的信息:
-- 1、查询员工工资小于1000的员工编号,及其部门编号
SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno;
-- 2、将上面的查询结果当做一张表，查询部门编号不在里面的部门编号
SELECT d.* FROM dept d
WHERE d. deptno NOT IN(SELECT deptno FROM emp WHERE s