前言
咋说呢，最近交接了一个XXX统计系统到我手上，点进去系统主页，看了下实现了哪些功能，页面看着很简单就那么几个统计模块，本来想着就那么几张报表的crud来着，看了下代码也还好体量也不大，于是乎美滋滋的随波逐流了，后来出现了一个bug说什么数据统计的不对，想着快速给他改掉，顺着控制层，一路摸索到mapper这，点进xml文件一看，好家伙我尼玛一个统计sql 400多行，故事的正片由此开始！
骚操作准备工作
准备如下这么一张数据表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id int NOT NULL AUTO_INCREMENT COMMENT '商品编号',
name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品名称',
price double DEFAULT NULL COMMENT '商品单价',
year int DEFAULT '0' COMMENT '商品库存',
month int DEFAULT NULL COMMENT '商品类型',
type text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '类别',
rate int DEFAULT '1' COMMENT '利率',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO goods VALUES (1, '00002', 2, 2022, 2, '文具', 2);
INSERT INTO goods VALUES (2, '00002', 22, 2022, 2, '手机', 22);
INSERT INTO goods VALUES (3, '00003', 3, 2022, 4, '电脑', 3);
INSERT INTO goods VALUES (4, '00003', 33, 2022, 4, '毛巾', 33);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
骚操作一，深入理解 SUM（）
求2022年各个月的营收，模拟环境：每种商品对应的利率可能都是不一样的，实际环境中可能 rate 字段会用一张独立的表去存储，那时的sql可能会更加复杂，此案例的列觉只为帮助大家更好的理解，sum 函数底层的执行过程
select year,month,sum(price*rate) sum from goods group by year,month;
由于写法是 sum(price*rate) 故分组后的中间状态是这样的，如下图一。根据 year、month 分组聚和成了如下这么俩条数据，那些未被聚合的字段比如：price、rate、type也是在一一对应着的，由于文具和手机对应的俩条数据被聚合成一条数据，所以我们可以使用聚合函数去操作这些字段
故最后的计算结果如下图
骚操作二， 深入理解 HAVING
追加需求：求2022年营收超过500的月份是哪个月？
骚操作一已经统计出来了各个月的营收，求超过500的营收是哪个月份只需对骚操作一的结果进行一个筛选就好了，先来列举常规几种操作
- 在代码中进行一个筛选
- 对骚操作一做一个子查询，然后使用 where 做一个数据筛选
select * from (select year,month,sum(price*rate) sum from goods group by year,month)a where a.sum>500;
- 使用 HAVING,对上点 2 做一个优化，下图俩条 sql 执行结果都一样
普及一下 HAVING 的用法：
- 对已经生成的结果级进行一个筛选，筛选条件只局限结果级中存在的列，何为结果级？举个例子：就是 where、group by 都已经作用过得到的结果，此时如果还想对数据做一个筛选，就可以用 having。
如果有的小伙伴还对 having 有点陌生，可以重点分析下下图一的第二条sql，对应的结果也在下图一
骚操作三，深入理解 CONCAT（）
求2022年2月到2022年5月的所有数据,但是年、月是分俩个字段存储的
select *,CONCAT(year,month) date from goods where CONCAT(year,month)>='20222' and CONCAT(year,month)'20225';
打破思维误区！直接在查询条件使用 CONCAT 函数拼接字段进行查询，就可以了，查询 sql 以及返回结果如下图一
骚操作四 ，深入理解sql列
在查出的结果级上面手动的添加一列，并赋予默认值，基本没咋用到，图一乐就行
select name,'自定义填充数据' customDate from goods GROUP BY name;
常规操作，IF、IFNULL、ROUND
这俩个函数也非常实用，当某些字段为null的时候，可以自定义一些默认值。ROUND(a,b)，为 a 保留 b 位小数。
select id,if(id=1,'-',id),IFNULL(id,0),ROUND(id/100,4) from goods;
关于mysql中的异或条件查询，避坑！！！！
最近再改这么一个sql：查询企业前十排名，力度精确到省、市、区县。由于这个接口很多地方都用到了，考虑到接口的兼容性，入参、出参的格式也不好做改动，当时的入参是这样的，areaCode 有可能是区县代码、也可能是省市代码。因此我们在做逻辑查询的时候，查询条件必定少不了 “或” 查询。
{
"year": "2022",
"month": "7",
"areaCode": "330110"
}
当时就是很简单的在 where 后面加了个 or ，但是实际跑下来感觉到查出的数据有点不对劲，sql本意是想：当 area_code 传的是省市代码，查出 2022 年月份小于 7 并且 trade_type 为出口的省市数据，当 area_code 传的是区县代码，查出 2022 年月份小于 7 并且 trade_type 为出口的区县数据，但是细看如下 sql ，我们会发现当 area_code 传的是区县代码的时候，代码片段2在代码片段1中直接失效了，最终的查询条件只有这一个： district_code = 330110 生效，于是修改sql成代码片段3那样就可以达到sql本意了。在java编码中条件短路的情况很常见，到了sql中同样也需要我们注意一下！
代码片段1
WHERE
year = 2022
AND month CAST( 7 AS UNSIGNED )
AND trade_type = 'ck'
AND area_code = 330110
OR district_code = 330110
代码片段2
year = 2022
AND month CAST( 7 AS UNSIGNED )
AND trade_type = 'ck'
AND area_code = 330110
代码片段3
WHERE
year = 2022
AND month CAST( 7 AS UNSIGNED )
AND trade_type = 'ck'
AND area_code = 330110
OR year = 2022
AND month CAST( 7 AS UNSIGNED )
AND trade_type = 'ck'
AND district_code = 330110
后面还有别的骚操作，本文会陆续更新～
Original: https://blog.csdn.net/qq_42875345/article/details/127769958
Author: 张子行的博客
Title: 盘点编写 sql 上的那些骚操作（针对mysql而言）
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/660961/
转载文章受原作者版权保护。转载请注明原作者出处！