---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-31 14:21:13 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([aa] varchar(9)) insert [tb] select '1+2+3+6' union all select '2+4+5+6+3' --------------开始查询-------------------------- select ID=IDENTITY(int,1,1),* into #t from tb select aa from ( select id,sum(cast (aa as int)) as aa from ( Select id,aa=substring(a.aa,b.number,charindex('+',a.aa+'+',b.number)-b.number) from #t a join master..spt_values b ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.aa) where substring('+'+a.aa,b.number,1)='+')t group by id)t
drop table #t ----------------结果---------------------------- /* aa ----------- 12 20(2 行受影响)*/
create table t(id int identity,v varchar(100)); insert into t (v) select '1+2+3' union all select '2+4+5';select * from t;alter table t add val int-- 这个存储过程可以实现多列更新的情况,可以用游标处理 alter trigger tgr_t on t instead of update as begin if @@ROWCOUNT =0 return ; declare @v int=0; declare @sql nvarchar(100); declare @id int if UPDATE(val) begin select top 1 @sql= ' select @v='+v,@id=id from deleted; exec sp_executesql @sql,N' @v int output',@v output; update t set val=@v where id=@id; end endupdate t set val=0 where id=1/* id v val ----------- ------------- ----------- 1 1+2+3 6 2 2+4+5 NULL */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-31 14:21:13
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([aa] varchar(9))
insert [tb]
select '1+2+3+6' union all
select '2+4+5+6+3'
--------------开始查询--------------------------
select ID=IDENTITY(int,1,1),* into #t from tb
select aa
from
(
select
id,sum(cast (aa as int)) as aa
from
(
Select
id,aa=substring(a.aa,b.number,charindex('+',a.aa+'+',b.number)-b.number)
from
#t a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.aa)
where
substring('+'+a.aa,b.number,1)='+')t
group by
id)t
drop table #t
----------------结果----------------------------
/* aa
-----------
12
20(2 行受影响)*/
create table t(id int identity,v varchar(100));
insert into t (v) select '1+2+3' union all select '2+4+5';select * from t;alter table t add val int-- 这个存储过程可以实现多列更新的情况,可以用游标处理
alter trigger tgr_t on t
instead of update
as
begin
if @@ROWCOUNT =0 return ;
declare @v int=0;
declare @sql nvarchar(100);
declare @id int
if UPDATE(val)
begin
select top 1 @sql= ' select @v='+v,@id=id from deleted;
exec sp_executesql @sql,N' @v int output',@v output;
update t set val=@v where id=@id;
end
endupdate t set val=0 where id=1/*
id v val
----------- ------------- -----------
1 1+2+3 6
2 2+4+5 NULL
*/