用事务实现函数效果

 

 

--事務XML控制

 

 

-- 用事务/XML实现函数效果

 

create table ta( id int , name varchar ( 50))

insert ta

select 1, 'a' union all

select 1, 'b' union all

select 1, 'a' union all

select 2, 'e' union all

select 2, 'f' union all

select 3, 'g' union all

select 3, 'h' union all

select 3, 'i' union all

select 3, 'j' union all

select 4, 'k' union all

select 4, 'm' union all

select 4, 'l' union all

select 5, 'A' union all

select 5, 'B' union all

select 5, 'C' union all

select 5, 'D' union all

select 5, 'E' union all

select 6, 'F' union all

select 6, 'G' union all

select 6, 'H' union all

select 6, 'K' union all

select 6, 'M'

 

-- 合并效果

/*

ID          Name

----------- ----------

1           a ,b,a

2           e,f

3            g ,h,i,j

4           k,m,l

5           A ,B,C,D,E

6           F ,G,H,K,M

 

(6 個資料列受到影響 )

 

*/

 

-- 方法 1( 不用函数实现更新、查询 ) 如下用于几列合并一列方法 1 比方法 2 效率高

 

declare @tb table ( id int , name varchar ( 50), con int identity ( 1, 1))

insert @tb

select * from ta

 

begin tran

while exists( select 1 from @tb)

    begin

        update a

        set a. name= a. name+ ',' + b. name

        from ta a , @tb b

        where a. id= b. id  and

        not exists( select * from @tb where id= b. id  and con< b. con )

 

        delete b

        from @tb b where not exists( select 1 from @tb where id= b. id  and con< b. con)

    end

 

select distinct id, 显示 = stuff ( name , 1, charindex ( ',' , name ), '' ) from ta

 

-- 以下用于更新 commit tran 替换 rollback tran

--update ta set name=stuff(name,1,charindex(',',name),'')

--commit tran

 

rollback tran

 

select * from ta

 

 

-- 方法 2( 通过创建函数实现查询更新、查询 )

create function ta_fun( @id int )

returns varchar ( 1000)

as

begin

    declare @sql varchar ( 1000)

    set @sql= ''

    select @sql= @sql+ ',' + name from ta where id= @id

    --print @sql

    return stuff ( @sql, 1, 1, '' )

end

 

select distinct id, 显示 = dbo. ta_fun( id) from ta

--select id, 显示 =dbo.ta_fun(id) from ta group by id

-- 以下用函数更新

update ta set name = dbo. ta_fun( id)

 

--drop table ta 删测试表

--drop function ta_fun  删测试函数

 

-- 以下用 SQL2005 实现方法

 

--XML 方法 1

SELECT *

FROM ( SELECT DISTINCT id FROM ta ) A

OUTER APPLY

    ( SELECT [name]= STUFF ( REPLACE ( REPLACE (( SELECT name FROM ta N WHERE id = A. id FOR XML AUTO ), '<N name="' , ',' ), '"/>' , '' ), 1, 1, '' )) N

 

--XML 方法 2

select

    ID, [Name]= stuff (( select ',' + Name from Ta where ID= a. ID for xml path ( '' )), 1, 1, '' )

from Ta a

group by ID