使用任意数量的小数位格式化 SQL 数字查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/652428/
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
Formatting an SQL numeric query result with an arbitrary number of decimal places
提问by Andrew Swan
I have a database table with these two columns:
我有一个包含这两列的数据库表：
- Amount: numeric (18,0)
- DecimalPlaces: numeric (18,0)
- 金额：数字 (18,0)
- DecimalPlaces：数字 (18,0)
This table can store amounts in various currencies, with the decimal place removed from the amount (I can't change this data model). For example, there might be two rows like this:
此表可以存储各种货币的金额，并从金额中删除小数位（我无法更改此数据模型）。例如，可能有两行是这样的：
- 1290, 2 (This is ￡12.90, needs to appear as "12.90")
- 3400, 0 (This is 3400 Japanese Yen, needs to appear as "3400")
- 1290, 2（这是￡12.90，需要显示为“12.90”）
- 3400, 0（这是3400日元，需要显示为“3400”）
I need an SQL query for both Oracle and SQL Server that will format each amount with the correct number of decimal places, preserving any trailing zeroes as illustrated above. I can't use stored procedures, a reporting tool, or Excel.
我需要一个针对 Oracle 和 SQL Server 的 SQL 查询，它将使用正确的小数位数格式化每个金额，并保留如上所示的任何尾随零。我不能使用存储过程、报告工具或 Excel。
回答by Thomas Jones-Low
Your problem is that there isn't an easy way to do this for both SQLServer and Oracle in one query.
您的问题是没有一种简单的方法可以在一个查询中同时为 SQLServer 和 Oracle 执行此操作。
The Correct way to do this for SQLServer is to use STR:
为 SQLServer 执行此操作的正确方法是使用 STR：
Select STR(Amount, 18, DecimalPlaces) from myTable;
The correct way to do this for Oracle is using to_char:
为 Oracle 执行此操作的正确方法是使用 to_char：
SELECT to_char (amount, '99999999999999.'||rpad('',DecimalPlaces, '0'))
from MyTable;
The queries presented by jms and Andrew won't work in an Oracle query because Oracle SQL uses LENGTH() not LEN(). And Oracle uses to_char() not Cast().
jms 和 Andrew 提供的查询在 Oracle 查询中不起作用，因为 Oracle SQL 使用 LENGTH() 而不是 LEN()。而 Oracle 使用 to_char() 而不是 Cast()。
回答by Martlark
How about?
怎么样？
select 12345 amount, 2 decimalPlaces, substr( to_char( 12345 ), 1, length (to_char( 12345 ) ) - 2 ) || '.' || substr( to_char( 12345 ), -2 ) result from dual /
选择 12345 数量，2 个小数位，substr(to_char(12345)，1，长度 (to_char(12345)) - 2) || '.' || substr( to_char( 12345 ), -2 ) 结果来自双 /
amount decimalPlaces result
---------- ------------- ------
12345 2 123.45
回答by Learning
In SQL server you can :
在 SQL 服务器中，您可以：
select stuff(convert(varchar,amount) ,
len(convert(varchar,amount)) - DecimalPlaces - 1, 0, ".")
回答by jason saldo
This is gross but worked for the current inputs on SQL server.
这很粗糙，但适用于 SQL 服务器上的当前输入。
select
substring(
CAST(
CAST(
(amount * power(-0.100000000000000000,decimalPlaces*1.000000000000000000)) as numeric(36,18)
)as varchar(30)
)
,1,len(cast(amount as varchar(20))) + (CASE WHEN decimalPlaces = 0 THEN 0 ELSE 1 END ))
from
myTable
回答by Andrew Swan
Martlark's answer for Oracle led me to this solution for SQL Server:
Martlark 对 Oracle 的回答让我找到了 SQL Server 的这个解决方案：
select
left(cast(Amount as varchar), len(cast(Amount as varchar)) - DecimalPlaces) +
left('.', DecimalPlaces) +
right(cast(OriginalCurrencyAmount as varchar), DecimalPlaces
) as FormattedAmount
from MyTable
回答by Andrew Swan
The best I've been able to come up with so far is:
到目前为止，我能想到的最好的是：
select Amount/power(10, DecimalPlaces) from MyTable
But it doesn't do exactly what I want:
但这并不完全符合我的要求：
- Oracle: the trailing zeroes are stripped, so US$15.00 looks like "15", not "15.00"
- SQL Server: a whole lot of extra trailing zeroes are added, so $23.99 looks like "23.99000000000" instead of "23.99"
- Oracle：去掉尾随的零，所以 15.00 美元看起来像“15”，而不是“15.00”
- SQL Server：添加了大量额外的尾随零，因此 $23.99 看起来像“23.99000000000”而不是“23.99”
