【mysql】 踩坑记录之derived(派生表)

前言

很多时候我们常常感觉到，不要你以为，mysql要它以为。记录派生表踩坑记录。

首先说明环境mysql5.7，脚本如下

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sal` float(255,0) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

insert into emp (name,sal,deptno) values ('emp1001',100,10);
insert into emp (name,sal,deptno) values ('emp1002',200,10);
insert into emp (name,sal,deptno) values ('emp1003',300,20);
insert into emp (name,sal,deptno) values ('emp1004',400,20);
insert into emp (name,sal,deptno) values ('emp1005',500,30);
insert into emp (name,sal,deptno) values ('emp1006',600,30);

出现的问题

我们要查询出每个部门工资最高的人

SELECT
    deptno,name,sal 
FROM
    ( SELECT * FROM emp ORDER BY sal DESC ) t 
GROUP BY deptno;

上条sql在msql5.7平台会报错

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 't.name' which is not functionally dependent on columns in
 GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 
 0.001000s

说的是t.name这个字段不在聚合的结果中。很多人可能不以为然，认为没人会这样写，但在大名鼎鼎的stackoverflow中，该实现的点赞数就有116个-由此可见其受众之广，仅次于后面提到的“方法二”（点赞数206个）。
https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

这个与sql_mode有关，在MySQL 5.7中，sql_mode调整为了select @@sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

其中，ONLY_FULL_GROUP_BY与group by语句有关，其要求select列表里只能出现分组列（即group by后面的列）和聚合函数（sum，avg，max等），这也是SQL92的标准。

如何解决

方法一：改写sql

SELECT
    e.deptno,
    name,
    sal 
FROM
    emp e,
    ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t 
WHERE
    e.deptno = t.deptno 
    AND e.sal = t.maxsal;

方法二：改写sql

SELECT
    a.deptno,
    a.name,
    a.sal 
FROM
    emp a
    LEFT JOIN emp b ON a.deptno = b.deptno 
    AND a.sal < b.sal 
WHERE
    b.sal IS NULL;

方法三：改写sql

SELECT
	* 
FROM
	emp 
WHERE
	sal IN (
	SELECT
		max( sal ) 
	FROM
		emp 
GROUP BY
	deptno)

文末鸡汤：

人生，是踏上了就回不了头的征程。要记住：面对，不一定最难过；孤独，不一定不快乐；得到，不一定能长久。珍惜拥有，用心生活，坦然无悔，超越自己，和生命一起精彩！