统一单位并计算采购面积

select  
    mass,
    case when unit like '英寸%' then '英寸'
    when unit like '厘米%' then '厘米' 
when unit = '米%' then '米'
when unit = '毫米%' then '毫米'
end unit
,
round (sum (
    convert( float,
        case when  unit like '厘米%' then convert (varchar(20),(leng*0.01)*(wide * 0.01)*qty)
        when unit like '英寸%' then convert(varchar(20),(leng*0.0254)*(wide * 0.0254)*qty)
        when unit = '米%' then convert (varchar(20),leng*wide)
        when unit = '毫米%' then convert (varchar(20),(leng*0.001)*(wide * 0.001)*qty)
        end 
    )
) * 1.550031 ,3)AS  采购面积

from CaiGouXinXi
where leng >0 and wide > 0 
group by mass,unit
order by 采购面积 desc

 1 select  
 2     mass,
 3     case when unit like '英寸%' then '英寸'
 4     when unit like '厘米%' then '厘米' 
 5 when unit = '米%' then '米'
 6 when unit = '毫米%' then '毫米'
 7 end unit
 8 ,
 9 round (sum (
10     convert( float,
11         case when  unit like '厘米%' then convert (varchar(20),(leng*0.01)*(wide * 0.01)*qty)
12         when unit like '英寸%' then convert(varchar(20),(leng*0.0254)*(wide * 0.0254)*qty)
13         when unit = '米%' then convert (varchar(20),leng*wide)
14         when unit = '毫米%' then convert (varchar(20),(leng*0.001)*(wide * 0.001)*qty)
15         end 
16     )
17 ) ,3)AS  采购面积
18 
19 from CaiGouXinXi
20 where leng >0 and wide > 0 
21 group by mass,unit
22 order by 采购面积 desc

表中部分字段:  leng wide qty unit mass

查询结果，如下图所示。