首页 \ 问答 \ 字母数字列上的MySQL MIN&MAX(MySQL MIN & MAX on alpha numeric column)

字母数字列上的MySQL MIN&MAX(MySQL MIN & MAX on alpha numeric column)

简单MAX查询:

SELECT MAX(Group) FROM acme

在数字列上正常工作。

是否可以在字母数字列上使用此选项并忽略除数字之外的任何内容?


Simple MAX query:

SELECT MAX(Group) FROM acme

Works fine on a numeric column.

Is it possible to use to this on an alpha-numeric column and ignore anything other than a number?


原文:https://stackoverflow.com/questions/38228544
更新时间:2021-10-10 21:10

最满意答案

是的,如果你在使用之前投射它像这样

mysql> SELECT MAX(CAST("123" AS UNSIGNED));
+------------------------------+
| MAX(CAST("123" AS UNSIGNED)) |
+------------------------------+
|                          123 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(CAST("abc" AS UNSIGNED));
+------------------------------+
| MAX(CAST("abc" AS UNSIGNED)) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT MAX(CAST("-123" AS SIGNED));
+-----------------------------+
| MAX(CAST("-123" AS SIGNED)) |
+-----------------------------+
|                        -123 |
+-----------------------------+
1 row in set (0.00 sec)

用“abc”替换字符串列名,并根据您的要求修改查询


yes if you cast it before use like this

mysql> SELECT MAX(CAST("123" AS UNSIGNED));
+------------------------------+
| MAX(CAST("123" AS UNSIGNED)) |
+------------------------------+
|                          123 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(CAST("abc" AS UNSIGNED));
+------------------------------+
| MAX(CAST("abc" AS UNSIGNED)) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT MAX(CAST("-123" AS SIGNED));
+-----------------------------+
| MAX(CAST("-123" AS SIGNED)) |
+-----------------------------+
|                        -123 |
+-----------------------------+
1 row in set (0.00 sec)

replace your string column name with "abc" and modify query acording to your requirment

相关问答

更多
  • 如果是C ++,则使用numeric_limits ,结束。 编辑:好吧,根据詹姆斯的评论,不是“结束”。 - 例外是模板和案例标签。 但是,我无法看到用于分钟或最大的案例标签或用于他们的模板,但我想我没有看到所有可能性...... 我想我的观点是, numeric_limits模板比max()和min()更有用... If C++, use numeric_limits, end of. EDIT: Okay, per the comment by James, not "end of." - excep ...
  • SELECT MIN(created_date) AS Min, MAX(created_date) AS Max -- Get min and max from the 1M rows FROM ( SELECT created_date FROM network ORDER BY created_date desc LIMIT 1000000 ) AS recent ...
  • 也许您的字段被视为文本,例如21将小于3(字母顺序)如果该字段是TEXT的类型尝试将值转换为INT() Maybe your fields are treated as text so, for example 21 will be smallest than 3 (alphabetical order) If that fields are type of TEXT try to cast values to INT()
  • 是的,LOW_VALUE和HIGH_VALUE会告诉您列中的最小值和最大值, 但是 : 它们被存储为RAW(32)列,所以含义不会立即显现 他们将是最后一次收集统计信息的表格,因此可能不准确(除非您在使用统计信息之前明确收集统计信息) 如果对列进行索引,那么MIN(a)和MAX(a)应该非常快,正如本例中T1有50000行并且在OBJECT_ID上索引的那样: SQL> select min(object_id) from t1; MIN(OBJECT_ID) -------------- ...
  • 是的,如果你在使用之前投射它像这样 mysql> SELECT MAX(CAST("123" AS UNSIGNED)); +------------------------------+ | MAX(CAST("123" AS UNSIGNED)) | +------------------------------+ | 123 | +------------------------------+ 1 row in set (0.00 sec) mysq ...
  • int max_num = -1; int min_num = -1 while (counter <= tmp){ cin >> input; //if smaller than previous number it is the minimum if (input < min_num || min_num == -1){ min_num = input; //counter++; => This operation is c ...
  • 从汇总值中获取整行的一般形式是: SELECT * FROM Races WHERE TotalCP = (SELECT MIN(TotalCP) FROM Races) 要么 SELECT r.* FROM ( SELECT MIN(TotalCP) t FROM Races ) m INNER JOIN Races r ON m.t = r.TotalCP 但是,在这种情况下,由于您使用MIN ,因此您可以对第一行进行排序: SELECT * FROM Races ORDER BY ...
  • min-max规范化可以通过以下方式完成: (df - df.min()) / (df.max() - df.min()) Out: Body: retail price Body: sensor resolution Body name Nikon D500 0.464643 ...
  • Series.unique返回一个numpy数组: df = pd.DataFrame({'A': [1, 2, 3, np.nan]}) df Out: A 0 1.0 1 2.0 2 3.0 3 NaN df['A'].unique() Out: array([ 1., 2., 3., nan]) 现在你调用df['A'].unique()的方法将是一个numpy方法。 如果数组中有nan则ndarray.min()返回nan 。 pd.Series.min()返回 ...
  • MySQL抱怨您使用子选择中列的别名,试试 SELECT ProductReference, MIN(SizeOrder) AS MinSizeID, MAX(SizeOrder) AS MaxSizeID, (SELECT SizeName FROM Size WHERE SizeOrder = MIN(Products.SizeOrder)) AS MinSizeText, (SELECT SizeName FROM Size WHERE SizeOrder = MAX(Products.SizeO ...

相关文章

更多

最新问答

更多
  • 如何避免iPhone应用程序泄漏?(How to avoid leaking in iPhone application?)
  • Rails引擎:在哪里定义仅用于测试的gems(Rails Engines: Where to define gems only used in testing)
  • 如何在android中的texviews中随机设置文本?(How to randomly settext in textviews in Android?)
  • PHP函数 - 用.00清理数字(PHP Function - cleaning up numbers with .00)
  • 如何在WPF中数据绑定到IGrouping?(How do I databind to a IGrouping in WPF?)
  • 在更新语句中增加时是否存在并发问题[复制](Are there concurrency issues when incrementing in update statement [duplicate])
  • RabbitMQ与NodeJS Addon for C ++应用程序集成(RabbitMQ vs NodeJS Addon for C++ Application Integration)
  • 使用CoreData执行乘法(聚合):如何?(Performing multiplication (aggregation) with CoreData: how to?)
  • 如何在Android中按位置显示Listview中的项目(How to display Item from a Listview by position in Android)
  • 无效的命令ProxyRequests设置jenkins时(Invalid command ProxyRequests when setting up jenkins)
  • C#,MySQL - 从表中检索行并在方法中填充变量,然后将Add()方法填充到List <>(C#, MySQL - Retrieve row from table and populate variables in method then Add() method to List<>)
  • 如何从内部拒绝承诺功能(How to reject a promise from inside then function)
  • javadoc for String [] args(javadoc for String[] args)
  • Laravel格式查询json结果(Laravel format query json result)
  • ImageIO - 从加载的图像中获取图像宽度和高度(ImageIO - get image Width and Height from loaded image)
  • 如何在Windows 7中批量重命名文件名,以便它们在结尾处具有数字顺序?(How do I batch-rename filenames in windows 7, so that they have numerical order at the end of them?)
  • 限制用户在Apple应用商店中撰写有关该应用的评论(Restrict user to write review about the app at Apple app store)
  • 亚马逊Kindle Fire中的java.lang.OutOfMemoryError(java.lang.OutOfMemoryError in Amazon Kindle Fire)
  • 如何使用C#在python中将字符串作为命令行参数传递(How to pass string as command line argument in python using C#)
  • 发生操作时使用ngrx-effect调用服务(Use ngrx-effect to call a service when an action occurs)
  • 在Android应用程序中集成所有社交资源(Integrating All Social Feeds In an Android App)
  • 哪些对象需要产前诊断?
  • 在React模板渲染上调用函数(Call function on React template render)
  • 如何将图标添加到SlidingTabLayout?(How to add Icons to SlidingTabLayout?)
  • 根据给定的数字数组创建一个几乎唯一的标识符(Create an almost unique identifier based on a given array of numbers)
  • 使用样式标签更改鼠标上方的文本颜色(Change text color on mouse over using the style tag)
  • Ruby String#{}不起作用(Ruby String #{} doesn't work)
  • 字符串和指针(Strings and pointers)
  • 中小学教师信息技术应用能力提升全员培训要求教师每五年不少于学时的培训标
  • 无法传递函数值来设置数组大小C ++(Can't pass a function value to set an array size C++)