select sum(case when ISNUMERIC(f1) = 1 then cast(f1 as int) else cast(f2 as int) end) from tb
declare @table table (f1 varchar(1),f2 varchar(2)) insert into @table select '5','dd' union all select 'a','6' union all select '7','8'select f1,f2,[sum]=case when isnumeric(f1)=1 and isnumeric(f2)=1 then cast(f1 as float)+cast(f2 as float) else '' end from @table /* f1 f2 sum ---- ---- ---------------------- 5 dd 0 a 6 0 7 8 15 */
create table tb(f1 varchar(10),f2 varchar(10)) insert into tb values('5', 'dd') insert into tb values('a', '6') go --如果f1,f2中至少一个是数值 select sum(case when ISNUMERIC(f1) = 1 then cast(f1 as int) else cast(f2 as int) end) from tb /* ----------- 11(所影响的行数为 1 行) */--保险起见,这样。 select sum(case when ISNUMERIC(f1) = 1 then cast(f1 as int) when ISNUMERIC(f2) = 1 then cast(f2 as int) else 0 end) from tb /* ----------- 11(所影响的行数为 1 行) */drop table tb
declare @table table (f1 varchar(1),f2 varchar(2)) insert into @table select '5','dd' union all select 'a','6' union all select '7','8'select [sum]= sum(case when isnumeric(f1)=1 then cast(f1 as float) else '' end) +sum(case when isnumeric(f2)=1 then cast(f2 as float) else '' end) from @table /* sum ---------------------- 26 */
--建表 create table #F(f1 varchar(10), f2 varchar(10)) --数据 insert into #F select '5','dd' union all select 'a','6' --显示 select case isnumeric(a.f1+b.f2) when 0 then a.f1+b.f2 when 1 then cast(cast(a.f1 as int)+cast(b.f2 as int) as varchar) end from #F as a ,#F as b--结果 ------------------------------ 5dd add 11 a6(4 行受影响)
create table #temp ( f1 varchar(10), f2 varchar(10) ) insert into #temp select '5','dd' union ALL select 'a','6' GO --SQL: SELECT (SELECT SUM(CAST(f1 AS INT)) FROM #temp WHERE ISNUMERIC(f1)=1) + (SELECT SUM(CAST(f1 AS INT)) FROM #temp WHERE ISNUMERIC(f1)=1)
declare @table table (f1 varchar(1),f2 varchar(2)) insert into @table select '5','dd' union all select 'a','6' union all select '7','8'select sum(cast(f1 as int)) from (select f1 from @table union all select f2 from @table) a where ISNUMERIC(f1)=1/*(3 行受影响)----------- 26
declare @table table (f1 varchar(1),f2 varchar(2))
insert into @table
select '5','dd' union all
select 'a','6' union all
select '7','8'select f1,f2,[sum]=case when isnumeric(f1)=1 and
isnumeric(f2)=1 then cast(f1 as float)+cast(f2 as float) else '' end
from @table
/*
f1 f2 sum
---- ---- ----------------------
5 dd 0
a 6 0
7 8 15
*/
insert into tb values('5', 'dd')
insert into tb values('a', '6')
go
--如果f1,f2中至少一个是数值
select sum(case when ISNUMERIC(f1) = 1 then cast(f1 as int) else cast(f2 as int) end) from tb
/*
-----------
11(所影响的行数为 1 行)
*/--保险起见,这样。
select sum(case when ISNUMERIC(f1) = 1 then cast(f1 as int)
when ISNUMERIC(f2) = 1 then cast(f2 as int)
else 0
end)
from tb
/*
-----------
11(所影响的行数为 1 行)
*/drop table tb
declare @table table (f1 varchar(1),f2 varchar(2))
insert into @table
select '5','dd' union all
select 'a','6' union all
select '7','8'select [sum]=
sum(case when isnumeric(f1)=1 then cast(f1 as float) else '' end)
+sum(case when isnumeric(f2)=1 then cast(f2 as float) else '' end)
from @table
/*
sum
----------------------
26
*/
--建表
create table #F(f1 varchar(10), f2 varchar(10))
--数据
insert into #F
select '5','dd'
union all select 'a','6'
--显示
select case isnumeric(a.f1+b.f2) when 0 then a.f1+b.f2 when 1 then cast(cast(a.f1 as int)+cast(b.f2 as int) as varchar) end
from #F as a ,#F as b--结果
------------------------------
5dd
add
11
a6(4 行受影响)
(
f1 varchar(10),
f2 varchar(10)
)
insert into #temp
select '5','dd' union ALL
select 'a','6'
GO
--SQL:
SELECT
(SELECT SUM(CAST(f1 AS INT)) FROM #temp WHERE ISNUMERIC(f1)=1)
+
(SELECT SUM(CAST(f1 AS INT)) FROM #temp WHERE ISNUMERIC(f1)=1)
insert into @table
select '5','dd' union all
select 'a','6' union all
select '7','8'select sum(cast(f1 as int)) from
(select f1 from @table union all select f2 from @table) a where ISNUMERIC(f1)=1/*(3 行受影响)-----------
26