文章分类:数据库
今天在写一个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
第一种: 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