linux 定义函数调用sql语句,mssql里自定义函数的用法一例

有表 t1:

id　pid

1　　1

1　　2

1　　3

2　　1

2　　2

3　　1

如何化成行如t2的格式:

id　 pid

1　　1,2,3

2　　1,2

3　　1

--建表

create table t1

(

id int,

pid varchar(10)

)

insert t1 values(10,'tom')

insert t1 values(10,'jerry')

insert t1 values(10,'aaa')

insert t1 values(10,'bbb')

insert t1 values(10,'ccc')

insert t1 values(20,'ddd')

insert t1 values(20,'eee')

insert t1 values(20,'fff')

insert t1 values(20,'ggg')

insert t1 values(20,'hhh')

insert t1 values(30,'iii')

insert t1 values(30,'jjj')

insert t1 values(30,'kkk')

select * from t1

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

--1.创建一个合并的函数

if exists (select * from sysobjects where objectproperty(object_id('dbo.connections'), 'isansinullson')=1)

drop function dbo.connections

go

create function connections(@id int)

returns varchar(8000)

as

begin

declare @str varchar(8000)

set @str=''

select @str=@str+','+cast(pid as varchar(8000)) from t1 where

set @str=right(@str,len(@str)-1)

return(@str)

end

go

--调用自定义函数得到结果

select distinct id,dbo.connections(id) from t1

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

--但是在sql server 7.0中无法使用自定义函数，怎么办？用游标

declare cursor_t1 cursor for select distinct id from t1

declare @id int

declare @pid varchar(8000)

if object_id('t2') is not null --构造t2表的结构

drop table t2

create table t2(id int,pid varchar(8000))

open cursor_t1

fetch next from cursor_t1 into @id

while @@fetch_status = 0

begin

set @pid =''

declare @len int

select @len=count(1) from t1 where

if object_id('tempdb..#tmp') is not null --构造临时表

drop table #tmp

select identity(int,1,1) as newid,id,pid into #tmp from t1 where

while @len>0 --通过循环拼接字符串

begin

select @pid=@pid +','+cast(pid as varchar) from #tmp where [newid]=@len

set @len=@len-1

end

drop table #tmp

set @pid=right(@pid,len(@pid)-1)

insert into t2 select @id, @pid --插入结果到t2表

fetch next from cursor_t1 into @id

end

close cursor_t1

deallocate cursor_t1

select * from t2

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

--有局限的做法，要求相同的id不能超过5条记录，但纯粹使用select实现，可以借鉴理解思路

select id,pid1+pid2+pid3+pid4+pid5 pids from

(

select t3.id,

max(case t3.ranking when 1 then t3.pid else '' end) pid1,

max(case t3.ranking when 2 then ','+t3.pid else '' end) pid2,

max(case t3.ranking when 3 then ','+t3.pid else '' end) pid3,

max(case t3.ranking when 4 then ','+t3.pid else '' end) pid4,

max(case t3.ranking when 5 then ','+t3.pid else '' end) pid5

from

(select t1.id,ranking=(select count(*) from t1 t2

where t2.id=t1.id and

t2.pid<=t1.pid), pid

from t1 t1) t3

group by t3.id) t4

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

--注意事项

--如果同一id拼接而成的pid长度超过256的话，请更改查询分析器里的默认字符数选项(菜单里的工具→选项→结果→每列最多字符数，默认是256)，不然会造成调用自定义函数后返回值长度跟定义的返回值长度不匹配的假象。