在MySQL中对包含varchar中的整数和多点的数据进行排序 [英] Sorting data which include integer and multi points in varchar in MySQL

问题描述

在我的项目中，我将文档存储在MySQL DB中.一个表包含标题编号(t_nm)和标题.我想按标题编号以正确的顺序列出该表.由于数据存储在varchar中，"SELECT * FROM table ORDER by t_nm"无法正常工作.

In my project I store documents in MySQL DB. A table contains title number (t_nm) and title. I want to list this table with correct order by title number. Because of the data stored in varchar " SELECT * FROM table ORDER by t_nm " does not work properly.

正确的查询是什么?

t_nm(varchar) 标题(varchar)

t_nm (varchar) title (varchar)

+------+----------------------+
| t_nm | title                |
+------+----------------------+
| 0.1   | A.MANIVANNAN        |
| 1.0   | M.ELUMALAI          |
| 10.3  | SAMPATH.R           |
| 1.2   | S.PAULRAJ           |
| 1.2.1 | A.ASHOK KUMAR       |
| 2.1   | S.JAYAKUMAR         |
| 9.0   | S.CINRAS            |
| 10.1  | P.MURUGAN           |

我想按这样的正确顺序对这些数据进行排序

I want to sort these data with the correct order like this

+------+----------------------+
| t_nm | title                |
+------+----------------------+
| 0.1   | A.MANIVANNAN        |
| 1.0   | M.ELUMALAI          |
| 1.2   | S.PAULRAJ           |
| 1.2.1 | A.ASHOK KUMAR       |
| 2.1   | S.JAYAKUMAR         |
| 9.0   | S.CINRAS            |
| 10.1  | P.MURUGAN           |
| 10.3  | SAMPATH.R           |

推荐答案

我没有安装mySQL，所以无法测试. 在排序之前先将varchar转换为十进制.

I don't have mySQL installed so I cannot test. Test casting the varchar into decimal before sorting.

它应该如下: SELECT * FROM table ORDER by CAST(t_nmAS DECIMAL(10,6));

it should go as follow : SELECT * FROM table ORDER by CAST(t_nmAS DECIMAL(10,6));

好吧，然后将其分解为以'.作为分度数的整数

Ok then explode it in an array with '." as the delemiter as integer

ex: SELECT * FROM table ORDER BY string_to_array(t_nmAS，'.'):: int [] ASC

ex : SELECT * FROM table ORDER BY string_to_array(t_nmAS , '.')::int[] ASC

这篇关于在MySQL中对包含varchar中的整数和多点的数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！