项目中有个需求是根据收款方来分类计算小计,并进行排序.
发现排序那一列中有小计和数字的时候,因为都是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