MAX(Column) 返回错误值 [英] MAX(Column) returns me a wrong value

问题描述

我想从我的发票表中检索最大发票编号，当我选择所有可以清楚地看到最大发票编号为10"的记录时，它返回以下内容.

I want to retrieve the maximum invoice number from my invoice table where it returns the following when I select all records where you can clearly see the maximum invoice no as "10".

select * from invoice

但是当我查询

select MAX(invoice_number) as maxinv from invoice

它返回我9".这是为什么?

It returns me "9". Why is that?

推荐答案

如果您的 invoice_number 存储为文本列，例如varchar(10).在这种情况下，根据字母顺序，9 将是最大值.

This situation can occur if your invoice_number is stored as a text column e.g. varchar(10). In that case, based on alphabetical order, 9 will be the maximum value.

理想情况下，您应该将要对其执行数值运算的值存储为数值数据类型，例如int.但是，如果由于某种原因您无法更改列数据类型，您可以在应用 MAX 之前尝试转换列，如下所示:

Ideally, you should be storing values on which you want to perform numerical operations as numeric datatypes e.g. int. However, if for some reason you cannot change column datatype, you can try casting the column before applying MAX, like so:

select max (convert(invoice_number, signed integer)) as maxinv from invoice

注意:我特别提到了您要对其执行数字运算的值"，因为在某些情况下，输入文本完全是数字，例如电话号码或信用卡号码，但也有在这种情况下，您不会想要添加 2 个电话号码或取信用卡号码的平方根.此类值应存储为文本.

NOTE: I specifically mention "values on which you want to perform numerical operations" because there are cases where the input text is entirely numeric, such as phone numbers or perhaps credit card numbers, but there is no scenario in which you would want to add 2 phone numbers, or take the square root of a credit card number. Such values should be stored as text.

这篇关于MAX(Column) 返回错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！