数据库中的经常会用到要过滤某个列的最值数据,比如,取数量最大的值,取时间最早的值等等,下面我们就来看看如何运用这个最值函数,特别注意,max函数或者min函数,参数都支持字符类型,这里就会有些坑存在,下面来实例描述下
一、应用场景
业务中的某张表,需要对时间分组后,取时间组内两个字段的最大值,那我们可以用max()函数对两个字段分别取最大值即可。
date | number | count |
2022-10-17 | 90 | 100 |
2022-10-17 | 8000 | 99 |
2022-11-17 | 1000 | 95 |
2022-11-17 | 800 | 198 |
2022-11-17 | 122 | 94 |
select month(date) as month ,max(number) as number, max(count) as count from T group by
month(date)
二、坑点
按这个sql得到的结果应该是有两条数据,分别是。按时间月份来分组,数据中有10与11月,所以两组,两组中,10月数据的number最大值为8000,count最大值为100,11月同理、(注意max函数只针对这一列数据进行求最大值,所以两列的最大值可能不是同一条数据的)
11 | 1000 | 198 |
10 | 8000 | 100 |
但是实际上我们得到的值是这样的:
11 | 800 | 95 |
10 | 90 | 99 |
这个结果是意想不到的,最后通过排查数据库的表结构设计发现,这两个字段的类型是字符类型,字符类型的排序就跟数值类型的排序就有很大差异了,这就是导致取最大值数据不符合原先设想的根因了
order by number (默认升序)得到结果如下:结果可以看出,字符类型的数值是从左往右一位位比较大小的,1>8>9所以,number字段中90就是最大值,10月则取的是90 ,11月同理解释
三、解决方案
- 将字段类型改成int,不用varchar类型
- 可以用CAST函数进行字段类型转换,sql如下:
select month(date) as month ,max(CAST(number as SIGNED)) as number, max(CAST(count as SIGNED)) as count from T group by
month(date)
总结:
- 用max函数时要注意参数的数据类型,会导致结果与设想不一致,切入点可以从字段的类型入手进行排查问题
- 通过 cast(value as type)或者convert(value,type)进行类型转换,转换以下几种类型:
-
binary:二进制类型;
-
char:字符类型;
-
date:日期类型;
-
time:时间类型;
-
datetime:日期时间类型;
-
decimal:浮点型;
-
signed:整型;
-
unsigned:无符号整型。