浅谈DQL之“相等问题”

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);

2.问题分析

对于这个问题，其实很简单

先求各个部门的最高薪资
再求出emp表中薪资等于最高工资的员工信息即可

问题解决

2.1求各个部门的最高薪资

 SELECT emp.deptno, MAX(emp.sal) FROM emp 
        GROUP BY emp.deptno

2. 2求出emp表中薪资等于最高工资的员工信息

SELECT * FROM emp 
    WHERE emp.sal IN(
        SELECT MAX(emp.sal) FROM emp 
          GROUP BY emp.deptno
    )ORDER BY deptno;

很明显，查询的结果是错误的

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;

5.总结

1.查询时，要充分考虑隐藏的条件
2.无论是关联查询，还是集合的in 都要考虑清楚，到底应该是哪些字段，保证同步