我字段类型 是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怎么只保留整数啊?
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
调用这个函数
可以在 在查询的时候 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
*/
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 也用ceiling函数取下整。
--这样看就直观了
select 1 union all
select 4.5566/*
1.0000
4.5566
*/
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
*/