一道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
    !](https://img-blog.csdnimg.cn/f4e0cc22dcef40ebb6f7f3e918916cdc.png)
  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 );
    
  • 7
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值