一个简单的sql语句

[fujunle]

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 a1/100 end) , sum(a3) a3 , a4 from tb group by a4

drop table tb
/*
a1 a3 a4
------------------------------- ----------- ----------
40 400 类二
0 200 类一

（所影响的行数为 2 行）

*/
为什么 类一 的值不是0.4呢？？

[jinjazz]

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 

[dobear_0922]

select       a1       =       sum(case       a2       when       0       then       a1       else       1.0*a1/100       end)       ,       sum(a3)       a3       ,       a4       from       tb       group       by       a4   

[fujunle]

select a1 = sum(case a2 when 0 then a1 else cast(a1 as float)/100*a3 end),sum(a3)a3,a4 from tb group by a4

drop table tb



cast(a1 as float)/100*a3
输出的值就不对了吧

[fujunle]

http://topic.csdn.net/u/20071114/12/e87507c7-2cc9-46ed-bd8e-99a6b56472a6.html
顺便一起给分

[中国风]

use test
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   

[kk19840210]

select a1=sum(case a2 when   0   then a1 else convert(float,a1)/100 end) ,sum(a3) a3  ,a4 from  tb   group  by a4