0.前言
今天在操作数据库时遇到了一个挺有意思的问题,特此记录
1.问题提出
现有一张emp表,表内数据如下
需求:求各部门薪水最高的员工信息
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',3000,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
![](https://img.haomeiwen.com/i3034232/104d902e5f6b7c5f.png)
2.问题分析
对于这个问题,其实很简单
先求各个部门的最高薪资
再求出emp表中薪资等于最高工资的员工信息即可
问题解决
2.1求各个部门的最高薪资
SELECT emp.deptno, MAX(emp.sal) FROM emp
GROUP BY emp.deptno
![](https://img.haomeiwen.com/i3034232/da9d1f70ddd93c9c.png)
2. 2求出emp表中薪资等于最高工资的员工信息
SELECT * FROM emp
WHERE emp.sal IN(
SELECT MAX(emp.sal) FROM emp
GROUP BY emp.deptno
)ORDER BY deptno;
![](https://img.haomeiwen.com/i3034232/b8a5c3edeb1a01f2.png)
很明显,查询的结果是错误的
3.问题再分析
查询结果的原因是:薪资和部门不是同时对等,即只比较了薪资,没有考虑部门这个因素
SELECT MAX(emp.sal) FROM emp
GROUP BY emp.deptno
只能获取一个最高薪资的集合(5000,3000,2850),然后与emp表中的薪资进行比较,结果就不会正确了。
正确的做法是:让一个部门所有人的薪资与
该
部门的最高薪资进行对比
4.问题解决
4.1 改进 in
同时考虑部门和薪资,先查询各个部门的最高薪资对应的 薪资及部门,
再求出emp表中 部门和薪资 同时等于最高薪资的 部门和薪资 的员工信息
SELECT * FROM emp
WHERE(emp.deptno,emp.sal)IN(
SELECT emp.deptno, MAX(emp.sal) FROM emp
GROUP BY emp.deptno
)ORDER BY deptno;
4.2 采用关联查询法
先查询各个部门的最高薪资对应的 薪资及部门,
将查出的结果作为新表,与emp表关联查询。
SELECT e1.* FROM emp e1,
(SELECT emp.deptno, MAX(emp.sal) sal FROM emp
GROUP BY emp.deptno)e2
WHERE e1.deptno=e2.deptno AND e1.sal=e2.sal
ORDER BY e1.deptno;
![](https://img.haomeiwen.com/i3034232/f73fcd22ba9bfca2.png)
5.总结
1.查询时,要充分考虑隐藏的条件
2.无论是关联查询,还是集合
的in 都要考虑清楚,到底应该是哪些字段,保证同步
网友评论