SQL如何把查询出来的记录保留整数

[沉默又寡言]

我字段类型 是INT类型的 可是这样一查出来

select '数量' as [BNAME],
sum(case when day(OQCTime)=1 then Inspection else 0 end) as [1]， 
。。。。
sum(case when day(OQCTime)=31 then Inspection else 0 end) as [31]
from TB

Inspection 是INT类型的 可是结果却是这样的 ：
BNAME 1 2 ....... 31
数量 0.0000 2.0000

怎么只保留整数啊？

[叶子]

select '数量' as [BNAME],
sum(case when day(OQCTime)=1 then ceiling(Inspection) else 0 end) as [1],
sum(case when day(OQCTime)=31 then ceiling(Inspection) else 0 end) as [31]
from TB

[Felixzhaowenzhong]

CEILING ( numeric_expression )
调用这个函数

[fuchaolin]

如果 Inspection 是int 得到的结果应该是 整数的， 如果一定要转成整数
可以在 在查询的时候 convert(int, sum(case when day(OQCTime)=1 then Inspection else 0 end))一下

[宸瑜]

楼上已回答

[叶子]

declare @TB table(OQCTime datetime,Inspection decimal(18,4))
insert into @TB
select '2012-01-01',2.3110 union all
select '2012-01-01',2.4110 union all
select '2012-01-02',1.2200 union all
select '2012-01-02',1.6000 

--向下取整用floor
--向上取整用ceiling
select '数量' as [BNAME],
sum(case when day(OQCTime)=1 then floor(Inspection) else 0 end) as [1], 
sum(case when day(OQCTime)=2 then floor(Inspection) else 0 end) as [2]
from @TB
/*
BNAME 1                                       2
----- --------------------------------------- ---------------------------------------
数量    6                                       4
*/

[沉默又寡言]

[Quote=引用 1 楼 maco_wang 的回复:]
SQL code

select '数量' as [BNAME],
sum(case when day(OQCTime)=1 then ceiling(Inspection) else 0 end) as [1],
sum(case when day(OQCTime)=31 then ceiling(Inspection) else 0 end) as [31]
from TB
[/Quote]结果还是没变····

[fuchaolin]

你只有在sum外面处理才有用 你把你表结构列出来看看

[沉默又寡言]

select '数量' as [BNAME],
sum(case when day(OQCTime)=1 then Inspection else 0 end) as [1]， 
。。。。
sum(case when day(OQCTime)=31 then Inspection else 0 end) as [31]
from TB WHERE ....
union all
select 'WPP' as [BNAME],
sum(case when day(OQCTime)=1 then WPP else 0 end) as [1],
...
sum(case when day(OQCTime)=31 then WPP else 0 end) as [31]
from TB WHERE ....

WPP字段类型是numeric(18, 4)

[沉默又寡言]

WPP字段要保留4位小数

[叶子]

你用了union all
把下面的WPP 也用ceiling函数取下整。

[叶子]

[Quote=引用 9 楼 overset 的回复:]
WPP字段要保留4位小数
[/Quote]
你用了union all 就相当于这是一个整体了，如果要下面的保留4位小数，上面的整数小数点后面也会出现4个0

--这样看就直观了
select 1 union all
select 4.5566

/*
1.0000
4.5566
*/

[fuchaolin]

那就没办法了，只能这样

[叶子]

如果你一定要上下的精度不同的话，只能用文本了。

select '数量' as [BNAME],
ltrim(sum(case when day(OQCTime)=1 then floor(Inspection) else 0 end)) as [1],

ltrim(sum(case when day(OQCTime)=31 then floor(Inspection) else 0 end)) as [31]
from TB WHERE ....
union all
select 'WPP' as [BNAME],
ltrim(sum(case when day(OQCTime)=1 then WPP else 0 end)) as [1],
...
ltrim(sum(case when day(OQCTime)=31 then WPP else 0 end)) as [31]
from TB WHERE ....

[叶子]

--原理上就是这样的
select ltrim(1) 
union all
select ltrim(4.5555)

/*
1
4.5555
*/