34,838
社区成员




--环境
create table test
(
id int,
did int
)
insert into test select 68, 70
insert into test select 69, 70
insert into test select 70, 70
insert into test select 80, 69
insert into test select 81, 69
--建立辅助函数
create function f_connstr
(
@col int,
@pro varchar(100)
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str + ',' + cast(id as varchar)
from test
where did = @col and ',' + @pro + ',' like '%,'+ cast(id as varchar) +',%'
return stuff(@str,1,1,'')
end
--声明参数变量
declare @pro varchar(100)
set @pro = '68,80,81'
--查询
select distinct did,dbo.f_connstr(did,@pro) as id
from test
--结果
69 80,81
70 68
--删除环境
drop function f_connstr
drop table test
--函数
create function fn_Ids(
@dqfzlid int
)
returns varchar(200)
as
begin
declare @Ids varchar(200)
set @Ids=''
select @Ids=@Ids+','+rtrim(id) from tab where dqfzlid =@dqfzlid
if @Ids<>''
set @Ids=stuff(@Ids,1,1,'')
return @Ids
end
go
--存储过程
create proc pr_Query
@Ids varchar(200)
as
exec('select dqfzlid,dbo.fn_Ids(dqfzlid) as Id from tab where id in ('+@Ids+') group by dqfzlid')
go
--调用
exec pr_Query '68,80,81'