首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >它错误地对百分比值进行了排序

问它错误地对百分比值进行了排序
EN

Stack Overflow用户
提问于 2021-10-05 07:27:30
回答 2查看 47关注 0票数 0

我在SQL Server中创建了我的项目。我想将我的记录按百分比值降序排列。我不知道为什么在少数情况下它不能工作。首先,我创建了TEMP Table来获取结果集。它工作正常,因为我的记录是按降序排列的:

代码语言:javascript
复制
DROP TABLE IF EXISTS #FeelingProductive55
CREATE TABLE #FeelingProductive55 (
[Age] varchar(300),
[How important would you say feeling productive is to your happin] varchar(300),
[The Quantity of feeling productivity is your happinnes] NUMERIC NOT NULL
)
INSERT INTO #FeelingProductive55
    SELECT DISTINCT  [Age],[How important would you say feeling productive is to your happin], 
            CAST(count([How important would you say feeling productive is to your happin]) OVER (PARTITION BY [How important would you say feeling productive is to your happin]) AS NUMERIC) as [The Quantity of feeling productivity is your happinnes]
    FROM Project3.[dbo].[fProductivityData]
    WHERE [Age] = '55+'
    ORDER BY [The Quantity of feeling productivity is your happinnes] DESC

我收到了:

它可以正常工作,因为我的记录是按降序排列的。

然后,我想以百分比值降序显示我的记录。为此,我使用了变量并进行了计算:

代码语言:javascript
复制
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 [The Percentage of feeling productivity is your happinnes] DESC

我的记录的结果是:

​

如你所见,我的记录的顺序是不合适的。

你可不可以告诉我,它不能按百分比值降序显示我的记录?如果你能修改我的代码或分享你的建议来使它正确,我将不胜感激,因为我试图在互联网上找到好的解决方案,但我找不到任何关于它的东西。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2021-10-05 10:44:06

为此,您可以使用窗口函数,并使用OVER子句。

然后按原始列排序,而不是按varchar百分比列排序

代码语言:javascript
复制
SELECT
  [Age],
  [How important would you say feeling productive is to your happin],
  FORMAT(
     [The Quantity of feeling productivity is your happinnes] * 100.0
      / SUM([The Quantity of feeling productivity is your happinnes]) OVER (),
   'P2') as [The Percentage of feeling productivity is your happinnes]
FROM #FeelingProductive55
ORDER BY
  [The Quantity of feeling productivity is your happinnes] DESC
票数 1
EN

Stack Overflow用户

发布于 2021-10-05 08:00:00

这是由于将% value连接为字符串所致。您可以覆盖列名称并仅在百分比值列上添加排序依据。

请参见示例。

代码语言:javascript
复制
-- Create temp table for temp data
CREATE TABLE #TempTable(
ID int,
Date datetime,
Name char(20),
Percentage decimal(18,2))
GO
-- Insert temp data
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (1,getdate(),'ABC',10)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (2,getdate(),'QWERT',80)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (3,getdate(),'ASDF',70)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (4,getdate(),'ZXCVB',35)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (5,getdate(),'YTYT',40)
GO
-- Write selecct query to get data with order by result
SELECT ID, Date, Name,Result FROM (
SELECT ID, Date, Name,Percentage,LTRIM(CONCAT(Percentage,' %')) as Result 
FROM #TempTable
) Report  ORDER BY Percentage DESC
GO

-- drop temp table.
DROP TABLE #TempTable
GO
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/69446385

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档