在MySQL中排序/排序 [英] Sorting/ordering in MySQL

问题描述

在尝试按列prog_id对表programs的内容进行排序时遇到一点问题，该列以以下格式保存每个程序的ID:

prog_id

1.0.1、1.0.2、1.0.3，...，1.0.10、1.0.11，...，1.1.0、1.1.1等

当我按prog_id排序时，我得到

1.0.1、1.0.10、1.0.11、1.0.2、1.0.3 ...

这对MySQL来说是正确的，但对于数据显示的顺序是不正确的. 我尝试使用另一列orderby，可以在其中保存索引和顺序，但是我必须手动输入值，并且表中有几千行，这将花费很长时间./p>

有什么技巧可以使我的数据以正确"的顺序显示?顺便说一句，我正在使用PHP& MySQL.

解决方案

不是最佳解决方案-

...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)

奇怪的解决方案，但请尝试-

...ORDER BY INET_ATON(prog_id)

I'm having a little problem with trying to sort the contents of a table programs by the column prog_id which holds the id of each program in the following format:

prog_id

1.0.1, 1.0.2, 1.0.3, ..., 1.0.10, 1.0.11, ..., 1.1.0, 1.1.1 etc

When I sort by prog_id i get

1.0.1, 1.0.10, 1.0.11, 1.0.2, 1.0.3 ...

which is correct as far as MySQL goes but not correct for the order in which the data should display. I tried using another column, orderby in which I could save an index and order by that but I would have to enter the values manually and there are a few thousand rows in my table which would take quite a long time to do.

Any tricks I could use to get my data to display in the "proper" order? BTW, I'm using PHP & MySQL.

解决方案

Not optimal solution -

...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)

Odd solution, but try it -

...ORDER BY INET_ATON(prog_id)

这篇关于在MySQL中排序/排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！