有三个表:
档案版本表:t_DocVersion
version_id version_name
1 V1.0
2 V2.0
版本访问表:t_DocAccess
version_id user_id
1 1
1 2
2 1
2 3
用户表:t_Users
user_id user_name
1 张三
2 李四
3 王五
现在想得到如下结果
version_name access
V1.0 张三,李四
V2.0 张三,王五
请问应该如何写SQL?
------解决方案--------------------
建表:
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