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

余性笃厚

已于 2023-11-23 11:49:19 修改

阅读量461

点赞数 7

分类专栏： MySQL 文章标签： sql 数据库

于 2023-11-22 15:38:56 首次发布

本文链接：https://blog.csdn.net/qq_54525549/article/details/134550066

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、需求描述：

在这里插入图片描述

二、模拟数据：

/*
 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测试：

SELECT * FROM mytable
业务需求是删除，是不是得先把满足条件的查出来，有了主键就可以把不满足条件的给删除；并且明显这个需求和分组有关，优先考虑分组，那分组有什么特殊情况呢？

如果你还没看出来，换个简单的：
默认是升序排列，于是我想，是不是有降序，只要我把每个分组中C字段最大的那条记录 提到所在组的最前面 是不是问题就解决了一半。（分组中 a 和 b 字段按照升序，使用的是默认的，c 字段显示指定降序）

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

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

好问题呀！因为我们知道MySQL其实和SQL规范其实有些差异的，于是我就在MySQL8.0以上的版本进行了相同查询测试。
测试结果：报了语法错误！

于是我单独把子查询提出来（只是为了测试）

去掉DESC排序关键字后，查询又出错：投影字段中出现不存在分组字段的问题

于是继续对比测试：投影字段a, b, c

投影a, b

投影a, b, c, d，字段 d 不在分组字段中

经过上面测试知道了，原来8.0优化了这么多东西，但是有一点，投影字段可以不在分组字段中，这个通过sql_mode可以设置以支持，但是分组排序在MySQL8.0以上就不支持了
那在MySQL8.0以上怎么办？MySQL8.0有窗口函数！主要就是几个函数的运用，解法和上面类似，稍微研究几个函数就懂了，关于窗口函数的就不多赘述了，感兴趣自己去研究吧

- 还有更简单方式吗？

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

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

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

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