在PHP或MySQL中对十进制进行排序 [英] Sort decimal in PHP or MySQL
问题描述
我正在开发分类帐应用程序.我的主要问题是我的客户的会计科目表带有这样的代码
I am developing a ledger application. My main problem is that my client has Chart of Account with code like this
1.1.1,
1.1.2
......
1.1.10,
1.1.11,
.........
使用PHP或MySQl我只能将它们排序为
Using PHP or MySQl I can only manage to sort them to
1.1.1,
1.1.10,
1.1.11,
1.1.2,
.......
是否有任何有关如何对其进行排序的帮助,以便在1.1.9之后出现1.1.10?
Any help on how to sort it so that 1.1.10 is coming after 1.1.9?
谢谢.
推荐答案
这很丑陋,但可以使用:
This is ugly, but it will work:
ORDER
BY SUBSTRING_INDEX(CONCAT( col ,'.'),'.',1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',2),'.',-1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',3),'.',-1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',4),'.',-1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',5),'.',-1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',6),'.',-1) + 0
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',7),'.',-1) + 0
要测试这些表达式,可以在SELECT中使用它们,并验证它们是否提取了正确的组件,并且它们的顺序正确:
To test these expressions, you can use them in a SELECT and verify they extract the right components, and they are ordered correctly:
SELECT col
, SUBSTRING_INDEX(CONCAT( col ,'.'),'.',1) + 0 AS p1
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',2),'.',-1) + 0 AS p2
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',3),'.',-1) + 0 AS p3
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',4),'.',-1) + 0 AS p4
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',5),'.',-1) + 0 AS p5
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',6),'.',-1) + 0 AS p6
, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',7),'.',-1) + 0 AS p7
FROM mytable
ORDER BY 2,3,4,5,6,7,8
我只解释重要的技巧",而不是解释它是如何工作的.
Rather than explain how this works, i'm just going to hit the important "tricks"
-
在结尾加上.".在上排结束时,您需要这样做,以免多次退回上一个位置,
append a trailing "." on the end of the col, you need that so you don't get back the last position multiple times,
使用SUBSTRING_INDEX检索直到第n个."的部分.
use SUBSTRING_INDEX to retrieve portion up to nth '.'
使用SUBSTRING_INDEX检索其尾随部分(向后读至前导点
use SUBSTRING_INDEX to retrieve trailing portion of that (reading backwards, to the leading dot
添加零,以将字符串转换为数值
add zero, to convert the string to a numeric value
这篇关于在PHP或MySQL中对十进制进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!