sql中的text数据类型转换后相加的问题民

文章分类:数据库

   今天在写一个sql语句的时候遇到一个问题，想将text类型的字段进行求和，所以必须先将text类型的字段转换成int类型，所以复习了一下转换函数：
   第一种：cast(字段名 as  目标数据类型)
   将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
      cast(cast(v.value AS CHAR(2)) as int ))
   整个sql语句如下：
     select fc.F_fault_mlevel, sum(cast(cast(v.value AS CHAR(2)) as int )) as '工作量'  from CUS_REQUEST_NEW r
inner join CUS_AUDIT_NEW a on a.REQUEST_ID = r.ID
inner join CUS_FORM_NEW f on f.FORM_ID = a.FORM_ID and f.FORM_ID = r.FORM_ID
inner join CUS_FORM_VALUE_NEW fv on f.FORM_ID = fv.CUS_FORM_NEW_FORM_ID
inner join CUS_VALUE_NEW v on v.VALUE_ID  = fv.values_VALUE_ID and v.COMP_ID='8'
inner join F_content fc on fc.F_content_id = r.REQUEST_NAME
group by fc.F_fault_mlevel
    第二种： conert(目标数据类型,字段名称)    
  将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
     convert(int,CONVERT(char(2),v.value))
   整个sql语句如下：
select fc.F_fault_mlevel, sum(convert(int,CONVERT(char(2),v.value))) as '工作量'  from CUS_REQUEST_NEW r
inner join CUS_AUDIT_NEW a on a.REQUEST_ID = r.ID
inner join CUS_FORM_NEW f on f.FORM_ID = a.FORM_ID and f.FORM_ID = r.FORM_ID
inner join CUS_FORM_VALUE_NEW fv on f.FORM_ID = fv.CUS_FORM_NEW_FORM_ID
inner join CUS_VALUE_NEW v on v.VALUE_ID  = fv.values_VALUE_ID and v.COMP_ID='8'
inner join F_content fc on fc.F_content_id = r.REQUEST_NAME
group by fc.F_fault_mlevel