sql有小数显示小数,没有小数显示整数
sql数据类型为numeric(12,4)里面保存的数字有整数有小数,
如何查询出有小数显示小数,没有小数只显示整数?
[解决办法]
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([col] NUMERIC(12,4))
INSERT #tb
SELECT 1.234 UNION ALL
SELECT 1 UNION ALL
SELECT 2.45 UNION ALL
SELECT 5 UNION ALL
SELECT 3.1
--------------开始查询--------------------------
SELECT [col]=CASE WHEN CEILING([col])=[col] THEN LTRIM(CEILING([col])) ELSE LTRIM([col]) END FROM #tb
----------------结果----------------------------
/*
col
1.2340
1
2.4500
5
3.1000
*/
是这个样子吗
[解决办法]
select case when 数字%1>0 then 数字%1 else 数字 end
[解决办法]
SELECT [col]=CAST(CAST([col] AS FLOAT)AS VARCHAR(10)) FROM #tb
SELECT cast([col] as float)[col] FROM #tb
create table #temp(
qty decimal(12,4)
)
insert into #temp
select 1
union select 1.12
union select 1.234
union select 1.2342
union select 1.3
select case
when right(qty,4)='0000' then left(qty,len(qty)-5)
when right(qty,3)='000' then left(qty,len(qty)-3)
when right(qty,2)='00' then left(qty,len(qty)-2)
when right(qty,2)<>'00' and right(qty,1)='0' then left(qty,len(qty)-1)
else left(qty,len(qty)) end "查询结果",qty "原数据" from #temp
drop table #temp
查询结果原数据
11.0000
1.121.1200
1.2341.2340
1.23421.2342
1.31.3000
