mysql - 如何在Mysql中按ASC对条形码字符串进行排序

Question

嗨，我有一个表名称self_instrument，其中有一列Id_number，即仪器的条形码..我想按照 Id_number 按升序显示记录像下面这样

ACA1/STD/01-->this is barcode
ACA2/STD/01
ACA3/STD/01

ACB1/STD/01
ACB2/STD/01
.
.
.
.

mysql查询是

SELECT Distinct Id_number  
FROM  `self_instrument` 
WHERE `Id_number` != ''   
ORDER BY if( LOCATE( '/', `Id_number` ) >0, 
            SUBSTRING( `Id_number` , LOCATE( '/', `Id_number` ) +1 ) ,
            concat( 'z', `Id_number` ) ) , 
        if( LOCATE( '/', `Id_number` ) >0, 
            SUBSTRING( `Id_number` , 1, 3 ) , 
            concat( 'z', `Id_number` ) ) , 
        CAST( if( LOCATE( '/', `Id_number` ) >0, 
                  SUBSTRING( `Id_number` , 4, LOCATE( '/', `Id_number` ) -4 ) , 
                  concat( 'z', `Id_number` ) ) AS UNSIGNED )

但是当我运行它时，它显示的输出如下

WT/CI-EQ/2011/500   -->these are some exceptional barcode in the records 
BLORE/DHTC/ALAB/50-600/01   --->same as above 
AC/INST/021     
AC/INST/021     
ACA1/STD/01         
ACA2/STD/01         
ACA3/STD/01     
ACA4/STD/01     
ACA5/STD/01     
ACA6/STD/01

因此，按照升序顺序，它需要显示先A后W和系列图案就好像ACA1/STD/01
ACA2/性病/01ACA3/STD/01

Answer 1

您首先按 / 之后的子字符串进行排序。我认为您希望这是最后一个标准:

SELECT Distinct Id_number  
FROM  `self_instrument` 
WHERE `Id_number` != ''   
ORDER BY 
        -- Sort first by 3-letter prefix 
        if( LOCATE( '/', `Id_number` ) >0, 
            SUBSTRING( `Id_number` , 1, 3 ) , 
            concat( 'z', `Id_number` ) ) , 
        -- Then by number after last `/`
        CAST(IF( LOCATE ('/', Id_number ),
                SUBSTRING_INDEX( Id_number, '/', -1), 
                CONCAT( 'z', Id_number) ) AS UNSIGNED ),
        -- Then by number after the prefix
        CAST( if( LOCATE( '/', `Id_number` ) >0, 
                  SUBSTRING( `Id_number` , 4, LOCATE( '/', `Id_number` ) -4 ) , 
                  concat( 'z', `Id_number` ) ) AS UNSIGNED ),
        -- Finally by everything after first `/`
        if( LOCATE( '/', `Id_number` ) >0, 
            SUBSTRING( `Id_number` , LOCATE( '/', `Id_number` ) +1 ) ,
            concat( 'z', `Id_number` ) )