建表:
create table t_DocVersion(version_id int, version_name varchar(20))insert into t_DocVersion
select 1 ,'V1.0' union all
select 2 ,'V2.0'create table t_DocAccess(version_id int, user_id int)insert into t_DocAccess
select 1 , 1 union all
select 1 ,2 union all
select 2 ,1 union all
select 2 ,3
create table t_Users(user_id int, user_name varchar(20))insert into t_Users
select 1 ,'张三' union all
select 2 ,'李四' union all
select 3 ,'王五'
go
合并函数:
--方法2. 函数,效率较高
if exists(select * from sys.objects where name = 'fn_mergeSTR')
drop function fn_mergeSTR
go create function dbo.fn_mergeSTR(@version_id int) --分隔符
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
--这里的user_name为需要合并的字段
select @str = @str + ','+tu.user_name
from t_DocAccess td,t_Users tu
where td.user_id = tu.user_id and td.version_id = @version_id
set @str = STUFF(@str,1,1,'')
return @str --返回值
end
go查询:
select version_name,
dbo.fn_mergeSTR(version_id) access
from t_DocVersion
/*
version_name access
V1.0 张三,李四
V2.0 张三,王五
*/
create table t_DocVersion(version_id int, version_name varchar(20))insert into t_DocVersion
select 1 ,'V1.0' union all
select 2 ,'V2.0'create table t_DocAccess(version_id int, user_id int)insert into t_DocAccess
select 1 , 1 union all
select 1 ,2 union all
select 2 ,1 union all
select 2 ,3
create table t_Users(user_id int, user_name varchar(20))insert into t_Users
select 1 ,'张三' union all
select 2 ,'李四' union all
select 3 ,'王五'
go
合并函数:
--方法2. 函数,效率较高
if exists(select * from sys.objects where name = 'fn_mergeSTR')
drop function fn_mergeSTR
go create function dbo.fn_mergeSTR(@version_id int) --分隔符
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
--这里的user_name为需要合并的字段
select @str = @str + ','+tu.user_name
from t_DocAccess td,t_Users tu
where td.user_id = tu.user_id and td.version_id = @version_id
set @str = STUFF(@str,1,1,'')
return @str --返回值
end
go查询:
select version_name,
dbo.fn_mergeSTR(version_id) access
from t_DocVersion
/*
version_name access
V1.0 张三,李四
V2.0 张三,王五
*/
create table #t_DocVersion(version_id int, version_name varchar(20))
insert into #t_DocVersion select 1 ,'V1.0' union all
select 2 ,'V2.0'
create table #t_DocAccess(version_id int, user_id int)
insert into #t_DocAccess select 1 , 1 union all
select 1 ,2 union all
select 2 ,1 union all
select 2 ,3
create table #t_Users(user_id int, user_name varchar(20))
insert into #t_Users select 1 ,'张三' union all
select 2 ,'李四' union all
select 3 ,'王五'select Version_name,
access=stuff((select ','+user_name from #t_users a,#t_DocAccess b where a.user_id=b.user_id and b.version_id=c.version_id for xml path('')),1,1,'')
from #t_DocVersion c
(col1 int, col2 int, col3 varchar(3))insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'
select a.col1,a.col2,
stuff((select ','+col3 from ym b
where b.col1=a.col1 and b.col2=a.col2
for xml path('')),1,1,'') 'col3'
from ym a
group by a.col1,a.col2
那就用这个吧,简单一点:
select version_name,
STUFF((
select + ','+tu.user_name
from t_DocAccess td,t_Users tu
where td.user_id = tu.user_id and td.version_id = t.version_id
for XML path('')),1,1,'') access
from t_DocVersion t
/*
version_name access
V1.0 张三,李四
V2.0 张三,王五
*/
SELECT t2.version_name,
user_name=stuff((select','+convert(varchar(20),user_name)FROM (select version_name,user_name from t_DocAccess a
inner join t_DocVersion v
on a.version_id=v.version_id
inner join t_Users u
on a.user_id=u.user_id
) t1 WHERE t1.version_name = t2.version_name for xml path('')),1,1,'')
FROM (select version_name,user_name from t_DocAccess a
inner join t_DocVersion v
on a.version_id=v.version_id
inner join t_Users u
on a.user_id=u.user_id
) t2
Group by t2.version_name