选择TEXT列的MAX INT值 [英] Select MAX INT value of TEXT column

问题描述

UserID    UserName  Password

1                abc               123
10               xyz               456
3                mno               254

SELECT MAX(UserId) AS UserId FROM UserLogin

当我运行此查询时，它给了我3个而不是10个

When I run this query it gives me 3 instead of 10

所有列均为TEXT数据类型

All columns are TEXT datatype

推荐答案

您的查询返回3，因为考虑到字典顺序，该值更大(以3开头的任何内容都比以1开头的任何内容都大，就像以b开头的内容大于以a开头的内容一样).

Your query is returning 3 because it is the larger value considering lexicographic order (anything starting with 3 is considered greater than anything starting with 1, just like something starting with b is greater than anything starting with a).

使用 VAL 函数转换TEXT列转换成数值:

Use the VAL function to convert the TEXT columns into numeric values:

SELECT MAX(VAL(UserId)) AS UserId FROM UserLogin

如果您担心性能，则应将此列设置为数字.考虑到您正在为表中的每一行调用一个函数.另外，它不会使用此列可能具有的任何索引.

If you're concerned about performance, you should make this column numeric, though. Take into account you're calling a function for every row in the table. Also, it won't be using any indexes this column may have.

这篇关于选择TEXT列的MAX INT值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！