ORACLE varchar字段中有汉字和数字,根据数字 order by

项目中有个需求是根据收款方来分类计算小计,并进行排序.

发现排序那一列中有小计和数字的时候,因为都是verchar类型的,所以进行排序之后发现9是在排在了10后面的.

SELECT *
  FROM (
        
        SELECT 1 AS SNO,
                TO_CHAR(ROW_NUMBER() OVER(ORDER BY DI.DISTRIBUTOR_NAME)) NUM,
                DI.DISTRIBUTOR_NAME,
                LCC.HANDLING_CHARGE_MONEY
          FROM LB_CONTRACT_INFO LCI
          LEFT JOIN LC_CALC_CONDITION LCC
            ON LCI.ID = LCC.CONTRACT_ID
          LEFT JOIN DISTRIBUTOR_INFO DI
            ON DI.ID = LCI.DISTRIBUTOR_ID
         WHERE LCI.ID IN ()
        UNION ALL
        SELECT 2 AS SNO,
                '小计' NUM,
                DI.DISTRIBUTOR_NAME,
                SUM(HANDLING_CHARGE_MONEY) AS HANDLING_CHARGE_MONEY
          FROM LB_CONTRACT_INFO LCI
          LEFT JOIN LC_CALC_CONDITION LCC
            ON LCI.ID = LCC.CONTRACT_ID
          LEFT JOIN DISTRIBUTOR_INFO DI
            ON DI.ID = LCI.DISTRIBUTOR_ID
         WHERE LCI.ID