SQL Server：舍入并添加百分号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21821407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server : round and add percent sign
提问by DWard
I have the following SQL query that is returning a result of 92.967013425802and I need it to be formatted like 93%and add the percent sign. I have tried changing the sum to round but I received an error
我有以下 SQL 查询返回结果，92.967013425802我需要将其格式化93%并添加百分号。我尝试将总和更改为舍入，但收到错误
The function 'round' is not a valid windowing function, and cannot be used with the OVER clause.
函数“round”不是有效的窗口函数，不能与 OVER 子句一起使用。
My query:
我的查询：
select
count(*) * 100.0 / sum(count(*)) over()
from
db_table_MetaData
group by
MetaValue
order by
MetaValue
Any help would be appreciated.
任何帮助，将不胜感激。
采纳答案by Marco
select
--Cast(Round(count(*) * 100.0 / sum(count(*)), 0) as nvarchar(5) + '%'
CAST(Round(count(*) * 100.0 / sum(count(*)), 0) as nvarchar(5)) + '%'
from
db_table_MetaData
This should do the trick.
这应该可以解决问题。
In essence you take the 08/15 ROUND()function to get your numeric value. After this you cast it into a nvarchar(x)and add a string to your string. However I have no method of checking my syntax right now.
本质上，您使用 08/15ROUND()函数来获取数值。在此之后，您将其转换为 anvarchar(x)并向您的字符串添加一个字符串。但是我现在没有办法检查我的语法。
回答by Bob
Strange you got not a valid function. Perhaps you didn't provide the correct parameters?
奇怪的是你没有一个有效的功能。也许您没有提供正确的参数？
This worked for me.
这对我有用。
select cast(Round('92.967013425802', 0) as nvarchar(10)) + '%'
回答by Dipendu Paul
I don't know what the real issue is, but when I tried the below query it worked fine. May be its the placement of parenthesis which went wrong with your query,
我不知道真正的问题是什么，但是当我尝试下面的查询时它运行良好。可能是您的查询出错的括号放置，
select
MetaValue, round(count(*) * 100.0 / sum(count(*)) over(),0)
from db_table_MetaData
group by MetaValue
order by MetaValue
check it working at SQL Fiddle: http://www.sqlfiddle.com/#!3/55c82/3
回答by DWard
Answer (count(*) * 100.0 / sum(count(*)) over(),1)
答案 (count(*) * 100.0 / sum(count(*)) over(),1)
回答by Infomorph
I would use the FORMAT function with parameter 'p':
我会使用带有参数“p”的 FORMAT 函数：
SELECT FORMAT(50.019/100.0,'p') as [Percentage]
This will give you a rounded result like:
这会给你一个四舍五入的结果，如：
Percentage
50.02 %
Check MSDN for more infos:
https://msdn.microsoft.com/de-de/library/hh213505.aspx
查看 MSDN 了解更多信息：https:
//msdn.microsoft.com/de-de/library/hh213505.aspx
回答by Shangshang
select
CONCAT(count(*) * 100.0 / sum(count(*)), '%') AS Percentage
from
db_table_MetaData
group by
MetaValue
order by
MetaValue
