create table a1( CYC nvarchar(12), RCY varchar(10)) select CYC,SUM( isnull(cast(rcy as decimal(18, 2)),0)) from a1 GROUP BY CYC ORDER BY CYC
create table a(cyc nvarchar(10),rcy varchar(5)) insert a select N'一厂', '20' union all select N'一厂', '15.5' union all select N'二厂', '25' union all select N'二厂', '' union all select N'三厂', '30' union all select N'三厂', '0' union all select N'四厂', '50' union all select N'四厂', '38' select cyc,sum(convert(decimal(18,2),(case when rcy='' then '0' else rcy end))) as rcy from a group by cyc CYC RCY 二厂 25.00 三厂 30.00 四厂 88.00 一厂 35.50
create table a(cyc nvarchar(10),rcy varchar(5)) insert a select N'一厂', '20' union all select N'一厂', '15.5' union all select N'二厂', '25' union all select N'二厂', '' union all select N'三厂', '30' union all select N'三厂', '0' union all select N'四厂', '50' union all select N'四厂', '38' select cyc,sum(convert(decimal(18,2),(case when rcy='' then '0' else rcy end))) as rcy from a group by cyc CYC RCY 二厂 25.00 三厂 30.00 四厂 88.00 一厂 35.50
FROM A GROUP BY CYC
消息 8114,级别 16,状态 5,第 1 行
从数据类型 nvarchar 转换为 numeric 时出错。
SELECT CYC,SUM(convert(int,isnull(RCY,0))) as RCY FROM A GROUP BY CYC
我的RCY字段类型是字符串类型的需要转换的,其中的空是空字符串'',而不是NULL
SELECT CYC,SUM(convert(decimal(18,6),isnull(replace(RCY,' ',''),0))) as RCY FROM A GROUP BY CYC
消息 8114,级别 16,状态 5,第 1 行
从数据类型 nvarchar 转换为 numeric 时出错。
CYC nvarchar(12),
RCY varchar(10))
select CYC,SUM( isnull(cast(rcy as decimal(18, 2)),0)) from a1
GROUP BY CYC ORDER BY CYC
insert a
select N'一厂', '20' union all
select N'一厂', '15.5' union all
select N'二厂', '25' union all
select N'二厂', '' union all
select N'三厂', '30' union all
select N'三厂', '0' union all
select N'四厂', '50' union all
select N'四厂', '38'
select cyc,sum(convert(decimal(18,2),(case when rcy='' then '0' else rcy end))) as rcy from a group by cyc
CYC RCY
二厂 25.00
三厂 30.00
四厂 88.00
一厂 35.50