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

它可以正常工作,因为我的记录是按降序排列的。
然后,我想以百分比值降序显示我的记录。为此,我使用了变量并进行了计算:
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我的记录的结果是:

如你所见,我的记录的顺序是不合适的。
你可不可以告诉我,它不能按百分比值降序显示我的记录?如果你能修改我的代码或分享你的建议来使它正确,我将不胜感激,因为我试图在互联网上找到好的解决方案,但我找不到任何关于它的东西。
发布于 2021-10-05 10:44:06
为此,您可以使用窗口函数,并使用OVER子句。
然后按原始列排序,而不是按varchar百分比列排序
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发布于 2021-10-05 08:00:00
这是由于将% value连接为字符串所致。您可以覆盖列名称并仅在百分比值列上添加排序依据。
请参见示例。
-- 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
GOhttps://stackoverflow.com/questions/69446385
复制相似问题