直接把统计条件写在where关键字后面不就可以了?! 如select sum(columnname) from tablname where columnname>200
--要满足你条件,可用存储过程加动态SQL,如Create procedure up_testSum @table nvarchar(50), @colName nvarchar(50), @Result int out as Declare @ExecSql nvarchar(4000) -- .................
set @ExecSql= 'Select @Result=Sum(Case when isnumeric('+@colName+')=1 then '+@colName+' else 0 end) from '+@table execute sp_executesql @ExecSql,N'@Result int out',@Result outgo --调用 Declare @getSum int Exec up_testSum '表名','合计字段名',@getSum out
Select @getSum as 聚合统计值
可以考虑在系统的聚合函数中使用case进行处理--例如: select sum(case when isnumeric(字段)=1 then 字段 else 0 end) from 表
create table a(id int,n nvarchar(10)) insert into a select 1,'1' insert into a select 1,'t' insert into a select 1,'4' gocreate function f_sum(@id nvarchar(50)) returns int as begin declare @i int set @i='0' declare @sql nvarchar(100) select @i=@i+case when isnumeric(n)=1 then cast(n as int) else 0 end from a return(@i) end goselect dbo.f_sum(id) as [sum] from a group by iddrop table a drop function f_sum
如select sum(columnname) from tablname where columnname>200
@table nvarchar(50),
@colName nvarchar(50),
@Result int out
as
Declare @ExecSql nvarchar(4000) -- .................
set @ExecSql= 'Select @Result=Sum(Case when isnumeric('+@colName+')=1
then '+@colName+' else 0 end) from '+@table
execute sp_executesql @ExecSql,N'@Result int out',@Result outgo
--调用
Declare @getSum int
Exec up_testSum '表名','合计字段名',@getSum out
Select @getSum as 聚合统计值
select sum(case when isnumeric(字段)=1 then 字段 else 0 end) from 表
insert into a select 1,'1'
insert into a select 1,'t'
insert into a select 1,'4'
gocreate function f_sum(@id nvarchar(50))
returns int
as
begin
declare @i int
set @i='0'
declare @sql nvarchar(100)
select @i=@i+case when isnumeric(n)=1 then cast(n as int) else 0 end
from a
return(@i)
end
goselect dbo.f_sum(id) as [sum] from a group by iddrop table a
drop function f_sum