一道SQL题引发的思考-真实业务模拟

一、需求描述：

二、模拟数据：

/*
 Source Server         : mysql57
 Target Server Type    : MySQL
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for mytable
-- ----------------------------
DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable`  (
  `A` int(11) NULL DEFAULT NULL,
  `B` int(11) NULL DEFAULT NULL,
  `C` int(11) NULL DEFAULT NULL,
  `D` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`D`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mytable
-- ----------------------------
INSERT INTO `mytable` VALUES (23, 23, 23, 1);
INSERT INTO `mytable` VALUES (23, 23, 24, 2);
INSERT INTO `mytable` VALUES (11, 20, 32, 3);
INSERT INTO `mytable` VALUES (11, 21, 32, 4);
INSERT INTO `mytable` VALUES (10, 20, 23, 5);
INSERT INTO `mytable` VALUES (10, 20, 21, 6);
INSERT INTO `mytable` VALUES (10, 20, 32, 7);

SET FOREIGN_KEY_CHECKS = 1;

三、分析过程：

- 在MySQL版本5.7测试：

1. SELECT * FROM mytable
   
2. 业务需求是删除，是不是得先把满足条件的查出来，有了主键就可以把不满足条件的给删除；并且明显这个需求和分组有关，优先考虑分组，那分组有什么特殊情况呢？
   
   如果你还没看出来，换个简单的：
   
3. 默认是升序排列，于是我想，是不是有降序，只要我把每个分组中C字段最大的那条记录 提到所在组的最前面 是不是问题就解决了一半。（分组中 a 和 b 字段按照升序，使用的是默认的，c 字段显示指定降序）
   

• 发现！！！在MySQL5.7中居然分组字段也能排序！！！我不知道你懂不懂，但是这个结果就说明了我们要结束这问题了，只需要重复最开始的操作，设定分组条件为 a, b 来个嵌套查询即可：
  
• 你学废了吗？？到这一步其实删除的需求也就解决了，因为满足条件的D字段为主键，主键已经有了，写个删除一句即可，最后有完整语句

- MySQL8.0以上测试：突然有人问我一个问题？为什么分组字段没有D，但是可以投影D字段！

1. 好问题呀！因为我们知道MySQL其实和SQL规范其实有些差异的，于是我就在MySQL8.0以上的版本进行了相同查询测试。
   测试结果：报了语法错误！
   
   于是我单独把子查询提出来（只是为了测试）
   
   去掉DESC排序关键字后，查询又出错：投影字段中出现不存在分组字段的问题
   
   于是继续对比测试：投影字段a, b, c
   
   投影a, b
   
   投影a, b, c, d，字段 d 不在分组字段中
   
   经过上面测试知道了，原来8.0优化了这么多东西，但是有一点，投影字段可以不在分组字段中，这个通过sql_mode可以设置以支持，但是分组排序在MySQL8.0以上就不支持了

2. 那在MySQL8.0以上怎么办？MySQL8.0有窗口函数！主要就是几个函数的运用，解法和上面类似，稍微研究几个函数就懂了，关于窗口函数的就不多赘述了，感兴趣自己去研究吧
   

- 还有更简单方式吗？

1. 分组后 having！在组内进行筛选操作，说实在的，MAX() 不应该只对字段进行操作吗，返回组内字段的最大值，但是 “居然可以改变记录的选择，换句话说居然影响组内顺序”
   
2. 然后我补全业务需求的代码，报错：不能先SELECT出同一表中的某些值，再UPDATE这个表(在同一语句中)
   
3. 于是， 通过中间表进行操作，居然可以了！

   	DELETE FROM mytable
   	WHERE d NOT IN ( SELECT t.d
   					 FROM ( SELECT d
   							FROM mytable
   							GROUP BY a, b
   				 		    HAVING MAX(c)) t);
   

   额外补充： 这种方式其实很不好，而且含义不清晰，MySQL对于SQL规范的实现比较灵活，在Oracle中，HAVING MAX( c ) 是会报错误：无效的关系运算符，直意 MAX( c ) 不满足布尔条件表达式，当然，这里只是提一句表达Oracle更加严谨哈哈

四、完整的MySQL语句：

1. MySQL8.0以下

   DELETE FROM mytable
   WHERE d NOT IN ( SELECT d
   				 FROM ( SELECT *
   	  			        FROM mytable
   				 		GROUP BY a, b, c DESC) t
   				 GROUP BY a, b);
   

2. MySQL8.0以上

   DELETE FROM mytable 
   WHERE d NOT IN ( SELECT d
   				 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY a, b ORDER BY c desc) as rn, d
   					    FROM mytable) t 
   				 WHERE t.rn = 1 );
   

3. MySQL8.0以下可以执行；MySQL8.0以上需要对sql_mode进行修改，以满足投影字段不在分组字段中也可以执行

   DELETE FROM mytable
   WHERE d NOT IN ( SELECT t.d
   				 FROM ( SELECT d
   						FROM mytable
   						GROUP BY a, b
   				 		HAVING MAX(c) ) t );