项目中有个需求是根据收款方来分类计算小计,并进行排序.
发现排序那一列中有小计和数字的时候,因为都是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 IN ()
GROUP BY DI.DISTRIBUTOR_NAME) T
ORDER BY DISTRIBUTOR_NAME, SNO, NUM
结果为:
后sql修改为:
SELECT *
FROM (SELECT T.*, DECODE(NUM, '小计', '99999999', NUM) ORDER_COLUMN
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 IN ()
GROUP BY DI.DISTRIBUTOR_NAME) T) TT
ORDER BY DISTRIBUTOR_NAME, SNO, TO_NUMBER(ORDER_COLUMN)
结果可以正常排序:
1、数据库字段为int类型:
使用order by 排序:
SELECT * FROM test22 ORDER BY ID;
结果为:
2、数据库字段为varchar类型:
使用order by 排序 结果为:
可以看出,varchar 类型字段排序时,9>5>11>10>1
先是比较了第一位的数,然后才去比较第二位的数
3、解决办法:
1)可以将数据库字段修改为int类型
2)如果不能修改数据库字段,可以将sql修改为如下(在字段后面加一个0)
3)order by 中varchar字段 加上to_number()函数
SELECT * FROM test22 ORDER BY to_number(id);
SELECT * FROM test22 ORDER BY ID+0;