在 SQL-Server 中存储百分比值的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1602318/
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
What should be the best way to store a percent value in SQL-Server?
提问by Shimmy Weitzhandler
I want to store a value that represents a percent in SQL server, what data type should be the prefered one?
我想在 SQL Server 中存储一个表示百分比的值，应该首选哪种数据类型？
采纳答案by Guti_Haz
decimal(p, s) and numeric(p, s)
十进制（p，s）和数字（p，s）
p (precision):
p（精度）：
The maximum total number of decimal digits that will be stored (both to the left and to the right of the decimal point)
将存储的小数位数的最大总数（小数点左侧和右侧）
s (scale):
s（规模）：
The number of decimal digits that will be stored to the right of the decimal point (-> s defines the number of decimal places)
将存储在小数点右侧的小数位数（-> s 定义小数位数）
0 <= s <= p.
0 <= s <= p。
- p ... total number of digits
- s ... number of digits to the right of the decimal point
- p-s ... number of digits to the left of the decimal point
- p ... 总位数
- s ... 小数点右边的位数
- ps ... 小数点左边的位数
Example:
例子：
CREATE TABLE dbo.MyTable
( MyDecimalColumn decimal(5,2)
,MyNumericColumn numeric(10,5)
);
INSERT INTO dbo.MyTable VALUES (123, 12345.12);
SELECT MyDecimalColumn, MyNumericColumn FROM dbo.MyTable;
Result:
结果：
MyDecimalColumn: 123.00 (p=5, s=2)
MyNumericColumn: 12345.12000 (p=10, s=5)
回答by gbn
You should use decimal(p,s)in 99.9% of cases.
您应该在 99.9% 的情况下使用decimal(p,s)。
Percent is only a presentation concept: 10% is still 0.1.
百分比只是一个表示概念：10% 仍然是 0.1。
Simply choose precision and scale for the highest expected values/desired decimal places when expressed as real numbers. You can have p = s for values < 100% and simply decide based on decimal places.
当表示为实数时，只需选择最高预期值/所需小数位的精度和比例。对于 < 100% 的值，您可以使用 p = s 并简单地根据小数位来决定。
However, if you do need to store 100% or 1, then you'll need p = s+1.
但是，如果您确实需要存储 100% 或 1，那么您将需要 p = s+1。
This then allows up to 9.xxxxxx or 9xx.xxxx%, so I'd add a check constraint to keep it maximum of 1 if this is all I need.
然后这最多允许 9.xxxxxx 或 9xx.xxxx%，所以如果这是我所需要的，我会添加一个检查约束以使其最大为 1。
回答by Aaron Bertrand
I agree, DECIMAL is where you should store this type of number. But to make the decision easier, store it as a percentage of 1, not as a percentage of 100. That way you can store exactly the number of decimal places you need regardless of the "whole" number. So if you want 6 decimal places, use DECIMAL(9, 8) and for 23.3436435%, you store 0.23346435. Changing it to 23.346435% is a display problem, not a storage problem, and most presentation languages / report writers etc. are capable of changing the display for you.
我同意， DECIMAL 是您应该存储此类数字的地方。但是为了更容易做出决定，请将其存储为 1 的百分比，而不是 100 的百分比。这样您就可以准确地存储所需的小数位数，而不管“整数”是多少。因此，如果您想要 6 位小数，请使用 DECIMAL(9, 8)，对于 23.3436435%，您存储 0.23346435。将其更改为 23.346435% 是显示问题，而不是存储问题，并且大多数演示语言/报告编写者等都能够为您更改显示。
回答by Shimmy Weitzhandler
I think decimal(p, s)should be used while s represents the percentage capability. the 'p' could of been even 1 since we will never need more than one byte since each digit in left side of the point is one hunderd percent, so the p must be at least s+1, in order you should be able to store up to 1000%. but SQL doesn't allow the 'p' to be smaller than the s.
我认为应该使用decimal(p, s)而 s 代表百分比能力。'p' 甚至可以是 1，因为我们永远不需要超过一个字节，因为点左侧的每个数字都是百分之一，所以 p 必须至少是 s+1，以便您应该能够存储高达 1000%。但 SQL 不允许“p”小于 s。
Examples: 28.2656579879% should be decimal(13, 12) and should be stored 00.282656579879 128.2656579879% should be decimal(13, 12) and should be stored 01.282656579879
例如： 28.2656579879% 应该是十进制（13, 12）并且应该被存储 00.282656579879 128.2656579879% 应该是十进制（13, 12）并且应该被存储 01.282656579879
28% should be stored in decimal(3,2) as 0.28 128% should be stored in decimal(3,2) as 1.28
28% 应以十进制 (3,2) 存储为 0.28 128% 应以十进制 (3,2) 存储为 1.28
Note: if you know that you're not going to reach the 100% (i.e. your value will always be less than 100% than use decimal(s, s), if it will, use decimal(s+1, s).
注意：如果你知道你不会达到 100%（即你的值总是小于 100% 比使用小数（s，s），如果可以，使用小数（s+1，s））。
And so on
等等
回答by OMG Ponies
The datatype of the column should be decimal.
列的数据类型应为十进制。
