select translator as '译者姓名',count(*) as '总派件次数',sum(convert(numeric(8,2),quantity)) as '总翻译字数',sum(case when DATEDIFF(mm,[date],getdate())<=6 then convert(numeric(8,2),quantity) else 0 end) as '近6个月派件字数', (sum(case when DATEDIFF(mm,[date],getdate())<=6 then convert(numeric(8,3),total) else 0 end)/sum(case when DATEDIFF(mm,[date],getdate())<=6 then convert(numeric(8,2),quantity) else 0 end))/0.14*100 as '近6个月平均得分' from t_paijian where unitprice!='dd' and total!='dd' and translation='英->中' Group by translator
记录一下。
case when , datediff,convert,sum的用法都用了,原来自己也可以写这样的语句