create table tb(A1 int, A2 int, A3 int, A4 varchar(10))
insert into tb values(20, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
insert into tb values(10, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
goselect a1 = sum(case a2 when 0 then a1 else a1/100 end) , sum(a3) a3 , a4 from tb group by a4drop table tb
/*
a1 a3 a4
------------------------------- ----------- ----------
40 400 类二
0 200 类一(所影响的行数为 2 行)*/
为什么 类一 的值不是0.4呢??
insert into tb values(20, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
insert into tb values(10, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
goselect a1 = sum(case a2 when 0 then a1 else a1/100 end) , sum(a3) a3 , a4 from tb group by a4drop table tb
/*
a1 a3 a4
------------------------------- ----------- ----------
40 400 类二
0 200 类一(所影响的行数为 2 行)*/
为什么 类一 的值不是0.4呢??
go
select a1 =
sum(case a2 when 0 then cast( a1 as decimal(18,5)) else cast(a1*1.0/100 as decimal(18,5)) end) ,
sum(a3) a3 , a4
from tb
group by a4
--*1.0改换为小数/再用cast转换为decimal
create table tb(A1 int, A2 int, A3 int, A4 varchar(10))
insert into tb values(20, 1, 100, '类一 ')
insert into tb values(20, 0, 200, '类二 ')
insert into tb values(10, 1, 100, '类一 ')
insert into tb values(20, 0, 200, '类二 ')
go select a1 = sum(case a2 when 0 then a1 else cast(a1 as float)/100 end) , sum(a3) a3 , a4 from tb group by a4 drop table tb
insert into tb values(20, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
insert into tb values(10, 1, 100, '类一')
insert into tb values(20, 0, 200, '类二')
go select a1=sum(case a2 when 0 then a1 else convert(float,a1)/100 end) ,sum(a3) a3 ,a4 from tb group by a4 drop table tb ---结果
a1 a3 a4
----------------------------------------------------- ----------- ----------
40.0 400 类二
0.30000000000000004 200 类一(所影响的行数为 2 行)