一个简单的sql语句

Question

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呢？？

Answer 1

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 

Answer 2

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

Answer 3

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
输出的值就不对了吧

Answer 4

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

Answer 5

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   

Answer 6

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