SQL Server计算百分比字段按降序排序异常问题求助

问题根本原因 #

你排序的字段[The Percentage of feeling productivity is your happinnes]是拼接了%的字符串类型，不是数值类型，SQL Server会按照字符串的字符顺序排序，而非数值大小排序，比如字符串"9%"会排在"10%"前面，就会出现排序不符合预期的问题。

修复后代码 #

你只需要把排序字段换成原始的数值计算逻辑即可，不需要排序格式化后的字符串：

DECLARE @Total4 float;
SET @Total4 = (SELECT SUM([The Quantity of feeling productivity is your happinnes]) as Total4 FROM #FeelingProductive55);
PRINT @Total4;
SELECT [Age],[How important would you say feeling productive is to your happin],
        LTRIM(CONCAT((CAST(CAST([The Quantity of feeling productivity is your happinnes] as float) / @Total4 as DECIMAL(32,2))*100), ' ','%')) as [The Percentage of feeling productivity is your happinnes]
FROM #FeelingProductive55
-- 排序使用原始数值计算，不要排序格式化后的字符串
ORDER BY CAST([The Quantity of feeling productivity is your happinnes] as float) / @Total4 DESC

额外优化建议 #

• 临时表插入时的ORDER BY是无效的，表是无序集合，插入时的排序不会保留，查询时需要排序必须在最终SELECT语句中声明
• 百分比格式化可以用SQL Server内置的FORMAT函数简化写法，示例：FORMAT(CAST([The Quantity of feeling productivity is your happinnes] as float)/@Total4, 'P2') as [The Percentage of feeling productivity is your happinnes]，P2代表保留两位小数的百分比格式，自动拼接%符号，不需要手动拼接

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