在PHP或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,
.........

Using PHP or MySQl I can only manage to sort them to

1.1.1, 
1.1.10, 
1.1.11,
1.1.2, 
.......

Any help on how to sort it so that 1.1.10 is coming after 1.1.9?

Thanks in advance.

if the sorting field is name then use the following ORDER BY clause

ORDER BY length(name), name

Assign the values to array and use natsort() to sort the values naturally.

$foo = array ('1.1.1', '1.1.2', '1.1.10', '1.1.11');
natsort ($foo);
print_r ($foo);

/*
Array
(
    [0] => 1.1.1
    [1] => 1.1.2
    [2] => 1.1.10
    [3] => 1.1.11
)
*/

?>

You need to pull out the digits between the decimals and treat them as numerics. Use SUBSTRING_INDEX to pull out the digits and CAST to turn them into numerics:

SELECT *
FROM myAccounts
ORDER BY
  CAST(SUBSTRING_INDEX(account_number, '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(account_number, '.', -2) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(account_number, '.', -1) AS UNSIGNED)

I had a similar problem and I've managed it as the following:

SELECT .... , CAST(SUBSTRING([column_name],1) AS UNSIGNED) AS myNum,  CAST(SUBSTRING([column_name],3) AS UNSIGNED) AS myDec
FROM ...
WHERE ...
ORDER BY myNum, myDec

In your case maybe you will need an additional "depth". Hope this helps you to get some insight.

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

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,

• use SUBSTRING_INDEX to retrieve portion up to nth '.'

• 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