为什么Round或Truncate函数不起作用? [英] Why Round or Truncate functions does'n work?
问题描述
大家好,
i在SQL中截断或舍入数据时遇到问题。
如果我舍入或截断这样的数据:Round(1.456,2)或Round( 1.456,0)或截断(1.456,2)... - 一切都很好。
但是b $
假设我有两个SQL一个用于测量单位,另一个用于存储。
测量表值
| id | measure | show_decimals( INT ( 1 ))|
- -------------------- ------------------------
| 1 |单位| 0 |
| 2 | kilo | 3 |
| 3 |米| 2 |
商店桌面值
| id | item | measurement_id | count_left_in_store( DECIMAL ( 16 , 6 ))|
- -------------------- -------------------------------------------------- ----------------------
| 1 | pencil | 1 | 987。 000000 (只是 987 单位 铅笔)|
SO
如果我使用这样的SQL查询:
SELECT TRUNCATE (s.count_left_in_store, m.show_decimals) as UnitsLeft
FROM store s
上的代码关键字> JOIN measurement m m.id = s.measurement_id
WHERE s.id = 1 ;
它仍然给了我987.000000 UnitsLeft而不仅仅是987 UnitsLeft。
我尝试过Truncate,Round,Floor函数 - 没有什么是相同的。
一切正常,然后我写Truncate(987.000000,0)或Truncate(s.count_left_in_store,0),但不适用于TRUNCATE(s.count_left_in_store,m.show_decimals)
为什么我不能在截断,圆形,楼层和其他数学函数中使用其他表格列值作为小数参数?
任何人都可以帮我吗?
测试它
DECLARE @ val DECIMAL ( 10 , 6 )= 987 。 000000
SELECT CONVERT (INT , @ val ) AS Intgr,< span class =code-keyword> CONVERT ( DECIMAL ( 10 , 2 ), @ val )Dec2, CONVERT (< span class =code-keyword> DECIMAL ( 10 , 0 ), @ val )Dec0
并找出原因......开个玩笑;)
您应该转换为目标数据类型。
看看这里: ROUND(sql) [ ^ ]
回合功能正在运行,但返回相同的数据类型;)
在你的情况下,我建议像这样创建存储过程(使用动态查询):
CREATE PROCEDURE GetStoreArticle
@ id INT
AS
BEGIN
DECLARE @ dec INT
SELECT @ dec = show_decimals FROM 度量 AS m INNER JOIN store AS s ON m.ID = s.measurement_id WHERE s.id = @ id
DECLARE @ sql NVARCHAR (MAX)= N '
SELECT s.id,s.item,CONVERT(DECIMAL(10,') + CONVERT ( VARCHAR ( 10 ), @ dec )+ ' ),s .count_left_in_store)AS UnitsLeft
FROM store AS s INNER JOIN measure AS m ON s.measurement_id = m.ID
WHERE s.id =' + CONVERT ( VARCHAR ( 10 ), @id )
EXEC ( @ sql )
END
用于测试的示例代码:
CREATE TABLE #measures(ID < span class =code-keyword> INT IDENTITY ( 1 , 1 ),测量 VARCHAR ( 30 ),show_decimals INT )
INSERT INTO #measures(measure,show_decimals)
VALUES (' unit', 0 ),(' kilo', 3 ),(' meter', 2 )
CREATE 表 #store(id INT IDENTITY ( 1 , 1 ),item VARCHAR ( 30 ),measurement_id INT ,count_left_in_store DECIMAL ( 16 , 6 ))
INSERT INTO #store(item,measurement_id,count_left_in_store)
VALUES (' < span class =code-string> pencil', 1 , 987 ),( ' sugar', 2 , 5879 ),(' UDP cable',< span class =code-digit> 3 , 22 )
DECLARE @ id INT = 3
DECLARE @ dec INT
SELECT @ dec = show_decimals FROM #measures AS m INNER JOIN #store AS s ON m.ID = s.measurement_id < span class =code-keyword> WHERE s.id = @ id
DECLARE @ sql NVARCHAR (MAX)= N '
SELECT s.id,s.item,CONVERT(DECIMAL(10,' + CONVERT ( VARCHAR ( 10 ), @ dec )+ ' ),s.count_left_in_store)AS UnitsLeft
FROM #store AS s INNER JOIN #measure AS m ON s.measurement_id = m.ID
WHERE s.id =' + CONVERT ( VARCHAR ( 10 ), @ id )
EXEC ( @ sql )
DROP TABLE #store
DROP 表 #measures
它也有效;)
试试这个
DECLARE @ val 十进制( 10 , 6 )= 987 。 120000 , @ DecPrecision int
set @ DecPrecision = 0
选择 Cast( @ val as Float )
选择 ROUND(转换( Float , @ val ), @ DecPrecision )
即使是Maciej Los也试图让你理解同样的事情。
我找到了解决方案,
而不是
SELECT TRUNCATE (s.count_left_in_store,m.show_decimals) as UnitsLeft
FROM store s
JOIN measurement m on m.id = s.measurement_id
WHERE s.id = 1 ;
i使用
SELECT TRUNCATE_TO(s.count_left_in_store,m.show_decimals) as UnitsLeft
FROM 存储s
JOIN 测量m m.id = s。 measurement_id
WHERE s.id = 1 ;
其中TRUNCATE_TO是我自己在sql中创建的函数,它是
FUNCTION TRUNCATE_TO(
nmbr DECIMAL ( 16 , 6 ),
dec INT ( 1 )UNSIGNED
) RETURNS CHAR ( 16 )
BEGIN
DECLARE R CHAR ( 16 ) DEFAULT ' 0';
SET R = CONVERT ( TRUNCATE (nmbr,dec), CHAR ( 16 ));
RETURN R;
END
感谢Maciej Los建议使用存储过程;)
Hi everyone,
i have a problem truncating or rounding data in SQL.
If i round or truncate data like this: Round(1.456, 2) or Round(1.456, 0) or Truncate(1.456, 2)... - everything is fine.
BUT
lets say i have two SQL tables one for units of measurement and the other one is store.
measurement table values
|id |measure |show_decimals (INT(1))| ---------------------------------------------- |1 |unit |0 | |2 |kilo |3 | |3 |meter |2 |
store table values
|id |item |measurement_id |count_left_in_store (DECIMAL(16,6)) | ---------------------------------------------------------------------------------------------- |1 |pencil |1 |987.000000 (its just simply 987 units of pencils) |
SO
if i am using sql query like this:
SELECT TRUNCATE(s.count_left_in_store, m.show_decimals) as UnitsLeft FROM store s JOIN measurement m on m.id = s.measurement_id WHERE s.id = 1;
it still gives me 987.000000 UnitsLeft instead of just 987 UnitsLeft.
I tried Truncate, Round, Floor functions - nothing still the same.
Everything works fine then i write Truncate(987.000000, 0) or Truncate(s.count_left_in_store, 0), but doesn't work with TRUNCATE(s.count_left_in_store, m.show_decimals)
Why i can't use other table column value as a decimal parameter in Truncate, Round, Floor and other math functions?
Can anyone help me out right there?解决方案Test it
DECLARE @val DECIMAL(10,6) = 987.000000 SELECT CONVERT(INT, @val) AS Intgr, CONVERT(DECIMAL(10,2), @val) Dec2, CONVERT(DECIMAL(10,0), @val) Dec0
and find out why... Just kidding ;)
You should convert to the destination data type.
Have a look here: ROUND (sql)[^]
Round function is working but returns the same data type ;)
[EDIT]
In your case i suggest to create stored procedure like this (using dynamic query):
CREATE PROCEDURE GetStoreArticle @id INT AS BEGIN DECLARE @dec INT SELECT @dec = show_decimals FROM measures AS m INNER JOIN store AS s ON m.ID = s.measurement_id WHERE s.id = @id DECLARE @sql NVARCHAR(MAX) = N' SELECT s.id, s.item, CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) + '), s.count_left_in_store) AS UnitsLeft FROM store AS s INNER JOIN measures AS m ON s.measurement_id = m.ID WHERE s.id = ' + CONVERT(VARCHAR(10),@id) EXEC (@sql) END
Sample code used for test:
CREATE TABLE #measures(ID INT IDENTITY(1,1), measure VARCHAR(30), show_decimals INT) INSERT INTO #measures (measure, show_decimals) VALUES('unit',0), ('kilo',3), ('meter', 2) CREATE TABLE #store (id INT IDENTITY(1,1), item VARCHAR(30), measurement_id INT, count_left_in_store DECIMAL(16,6)) INSERT INTO #store (item,measurement_id,count_left_in_store ) VALUES('pencil',1 , 987), ('sugar', 2, 5879), ('UDP cable', 3, 22) DECLARE @id INT = 3 DECLARE @dec INT SELECT @dec = show_decimals FROM #measures AS m INNER JOIN #store AS s ON m.ID = s.measurement_id WHERE s.id = @id DECLARE @sql NVARCHAR(MAX) = N' SELECT s.id, s.item, CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) + '), s.count_left_in_store) AS UnitsLeft FROM #store AS s INNER JOIN #measures AS m ON s.measurement_id = m.ID WHERE s.id = ' + CONVERT(VARCHAR(10),@id) EXEC (@sql) DROP TABLE #store DROP TABLE #measures
It works as well ;)
Try This
DECLARE @val Decimal(10,6) = 987.120000,@DecPrecision int set @DecPrecision=0 select Cast(@val as Float) select ROUND(Convert(Float,@val),@DecPrecision)
Even Maciej Los was trying to make you understand the same thing.
i found a solution,
instead of
SELECT TRUNCATE(s.count_left_in_store, m.show_decimals) as UnitsLeft FROM store s JOIN measurement m on m.id = s.measurement_id WHERE s.id = 1;
i used
SELECT TRUNCATE_TO(s.count_left_in_store, m.show_decimals) as UnitsLeft FROM store s JOIN measurement m on m.id = s.measurement_id WHERE s.id = 1;
where TRUNCATE_TO is my own created function in sql which is
FUNCTION TRUNCATE_TO( nmbr DECIMAL(16,6), dec INT(1) UNSIGNED ) RETURNS CHAR(16) BEGIN DECLARE R CHAR(16) DEFAULT '0'; SET R = CONVERT(TRUNCATE(nmbr, dec), CHAR(16)); RETURN R; END
Thanks Maciej Los for advice to use a stored procedures ;)
这篇关于为什么Round或Truncate函数不起作用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!