SQL使用Format()格式化计算列后排序异常问题求助

你猜的没错，Format()函数确实会把数值转换成字符串，排序时自然会按字符串的首字符顺序来，而非数值大小。下面给你两个直接可用的解决方案：

方案1：直接用原始计算值排序 #

不需要修改SELECT里的格式化显示字段，只需要在查询末尾加上ORDER BY，用未格式化的原始计算式来排序，这样既保留格式化后的显示效果，又能按实际数值大小排序。

修改后的完整SQL：

Select 
p.c_Code as 'Product', 
p.c_description as 'Description', 
b.c_code 'Branch', 
format(pbs.c_stocklevel, 'n2') as 'Stock Level', 
Format(pbic.c_averagecost, 'n2') as 'Average Cost', 
Format((pbs.c_Stocklevel * pbic.c_averagecost),'n2') as tv

from t_productbranchstatistics pbs
join t_product p on p.c_id = pbs.c_product
join t_branch b on b.c_id = pbs.c_branch
join t_product_branchinfo pbi on pbi.c__owner_  = p.c_id and pbi.c_branch = b.c_id
join t_product_branchinfo_costings pbic on pbic.c_id = pbi.c_costings
where pbs.c_branch = @[_CurrentBranch._ID]
ORDER BY pbs.c_Stocklevel * pbic.c_averagecost DESC -- 改成ASC就是升序，按需选择

方案2：新增原始数值字段用于排序 #

如果觉得重复写计算式麻烦，可以在SELECT里额外加一个未格式化的原始数值字段，用它来排序。要是不想显示这个字段，也可以嵌套子查询隐藏它。

带原始字段的写法（可显示原始值） #

Select 
p.c_Code as 'Product', 
p.c_description as 'Description', 
b.c_code 'Branch', 
format(pbs.c_stocklevel, 'n2') as 'Stock Level', 
Format(pbic.c_averagecost, 'n2') as 'Average Cost', 
Format((pbs.c_Stocklevel * pbic.c_averagecost),'n2') as tv,
pbs.c_Stocklevel * pbic.c_averagecost as tv_raw -- 新增原始数值字段

from t_productbranchstatistics pbs
join t_product p on p.c_id = pbs.c_product
join t_branch b on b.c_id = pbs.c_branch
join t_product_branchinfo pbi on pbi.c__owner_  = p.c_id and pbi.c_branch = b.c_id
join t_product_branchinfo_costings pbic on pbic.c_id = pbi.c_costings
where pbs.c_branch = @[_CurrentBranch._ID]
ORDER BY tv_raw DESC

隐藏原始字段的写法（仅用于排序） #

SELECT Product, Description, Branch, `Stock Level`, `Average Cost`, tv
FROM (
    Select 
    p.c_Code as 'Product', 
    p.c_description as 'Description', 
    b.c_code 'Branch', 
    format(pbs.c_stocklevel, 'n2') as 'Stock Level', 
    Format(pbic.c_averagecost, 'n2') as 'Average Cost', 
    Format((pbs.c_Stocklevel * pbic.c_averagecost),'n2') as tv,
    pbs.c_Stocklevel * pbic.c_averagecost as tv_raw
    from t_productbranchstatistics pbs
    join t_product p on p.c_id = pbs.c_product
    join t_branch b on b.c_id = pbs.c_branch
    join t_product_branchinfo pbi on pbi.c__owner_  = p.c_id and pbi.c_branch = b.c_id
    join t_product_branchinfo_costings pbic on pbic.c_id = pbi.c_costings
    where pbs.c_branch = @[_CurrentBranch._ID]
) AS sub
ORDER BY tv_raw DESC

内容的提问来源于stack exchange，提问作者Kalvas