今天在写一个sql语句的时候遇到一个问题,想将text类型的字段进行求和,所以必须先将text类型的字段转换成int类型,所以复习了一下转换函数:
第一种:[color=red]cast(字段名 as 目标数据类型)[/color]
将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
[color=red] cast(cast(v.value AS CHAR(2)) as int )) [/color]
整个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
第二种:[color=red] conert(目标数据类型,字段名称) [/color]
将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
[color=red]convert(int,CONVERT(char(2),v.value))[/color]
整个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
说明:按照“故障等级”分类统计“工作量”
第一种:[color=red]cast(字段名 as 目标数据类型)[/color]
将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
[color=red] cast(cast(v.value AS CHAR(2)) as int )) [/color]
整个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
第二种:[color=red] conert(目标数据类型,字段名称) [/color]
将text类型的v.value转换成int型则需先将text转换成char然后再转换成int型
[color=red]convert(int,CONVERT(char(2),v.value))[/color]
整个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
说明:按照“故障等级”分类统计“工作量”