有这样一张表,我现在要统计Code=2 的Num数量,Num中存在非数字字符,
如果不是数字为0处理,
Code Num
1 1
2 1
3 2
4 d
2 e
2 3
结果
Num 应该为 4
请问怎么做
------解决方案--------------------
select sum(case when ISNUMERIC(num)=1 then num else 0 end) from #ad where Code=2
------解决方案--------------------
select sum(Num) from 表名 where Code=2 and isnumeric(Num)=1
------解决方案--------------------
wgzaaa() 正解
------解决方案--------------------
select sum(case when ISNUMERIC(isnull(num,0))=1 then num else 0 end) from #ad where Code=2
------解决方案--------------------
select count(num) from tablename where code=1 and IsNUMERIC(num)=1
------解决方案--------------------
select sum(case when ISNUMERIC(isnull(num,0))=1 then convert(int,num) else 0 end) from #ad where Code=2
------解决方案--------------------
select sum(convert(int,num)) from tablename where code= '2 ' and IsNUMERIC(num)=1
------解决方案--------------------
select count(case when ISNUMERIC(num)=1 then num else 0 end) from #ad where Code=2