背景：在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开id。如，当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设备从属多个项目时，很多人都会在员工表中加入一个deptids varchar(1000)列（本文以员工从属多个部门为例），用以保存部门编号列表（很明显这不符合第一范式，但很多人这样设计了，在这篇文章中我们暂不讨论在这种应用场景下，如此设计的对与错，有兴趣的可以在回复中聊聊），然后我们在查询列表中需要看到这个员工从属哪些部门。
初始化数据：
部门表、员工表数据：
复制代码 代码如下:
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[department]’))
drop table [dbo].department
go
–部门表
create table department
(
id int,
name nvarchar(50)
)
insert into department(id,name)
select 1,’人事部’
union
select 2,’工程部’
union
select 3,’管理部’
select * from department
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[employee]’))
drop table [dbo].employee
go
–员工表
create table employee
(
id int,
name nvarchar(20),
deptids varchar(1000)
)
insert into employee(id,name,deptids)
select 1,’蒋大华’,’1,2,3′
union
select 2,’小明’,’1′
union
select 3,’小华’,”
select * from employee
希望得到的结果：
解决方法：
第一步，是得到如下的数据。即将员工表集合与相关的部门集合做交叉连接，其中使用了fun_splitids函数（作用是将ids分割成id列表），然后员工集合与这个得到的集合做交叉连接
复制代码 代码如下:
select e.*,isnull(d.name,”) as deptname
from employee as e
outer apply dbo.fun_splitids(e.deptids) as did
left join department as d on did.id=d.id;
第二步，已经得到了如上的数据，然后要做的就是根据id分组，并对deptname列做聚合操作，但可惜的是sql server还没有提供对字符串做聚合的操作。但想到，我们处理树形结构数据时，用cte来做关系数据，做成有树形格式的数据，如此我们也可以将这个问题转换成做树形格式的问题，代码如下：
复制代码 代码如下:
;with employet as(
–员工的基本信息(使用outer apply将多个id拆分开来,然后与部门表相关联)
–此时已将员工表所存的ids分别与部门相关联,下面需要将此集合中的deptname聚合成一个记录
select e.*,isnull(d.name,”) as deptname
from employee as e
outer apply dbo.fun_splitids(e.deptids) as did
left join department as d on did.id=d.id
),mike as(
select id,name,deptids,deptname
,row_number()over(partition by id order by id) as level_num
from employet
),mike2 as(
select id,name,deptids,cast(deptname as nvarchar(100)) as deptname,level_num
from mike
where level_num=1
union all
select m.id,m.name,m.deptids,cast(m2.deptname+’,’+m.deptname as nvarchar(100)) as deptname,m.level_num
from mike as m
inner join mike2 as m2 on m.id=m2.id and m.level_num=m2.level_num+1
),maxmikebyidt as(
select id,max(level_num) as level_num
from mike2
group by id
)
select a.id,a.name,a.deptids,a.deptname
from mike2 as a
inner join maxmikebyidt as b on a.id=b.id and a.level_num=b.level_num
order by a.id option (maxrecursion 0)
结果如下：
全部sql：
复制代码 代码如下:
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[department]’))
drop table [dbo].department
go
–部门表
create table department
(
id int,
name nvarchar(50)
)
insert into department(id,name)
select 1,’人事部’
union
select 2,’工程部’
union
select 3,’管理部’
select * from department
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[employee]’))
drop table [dbo].employee
go
–员工表
create table employee
(
id int,
name nvarchar(20),
deptids varchar(1000)
)
insert into employee(id,name,deptids)
select 1,’蒋大华’,’1,2,3′
union
select 2,’小明’,’1′
union
select 3,’小华’,”
select * from employee
–创建一个表值函数，用来拆分用逗号分割的数字串，返回只有一列数字的表
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[fun_splitids]’))
drop function [dbo].fun_splitids
go
create function dbo.fun_splitids(
@ids nvarchar(1000)
)
returns @t_id table (id varchar(36))
as
begin
declare @i int,@j int,@l int,@v varchar(36);
set @i = 0;
set @j = 0;
set @l = len(@ids);
while(@j < @l)
begin
set @j = charindex(‘,’,@ids,@i+1);
if(@j = 0) set @j = @l+1;
set @v = cast(substring(@ids,@i+1,@j-@i-1) as varchar(36));
insert into @t_id values(@v)
set @i = @j;
end
return;
end
go
;with employet as(
–员工的基本信息(使用outer apply将多个id拆分开来,然后与部门表相关联)
–此时已将员工表所存的ids分别与部门相关联,下面需要将此集合中的deptname聚合成一个记录
select e.*,isnull(d.name,”) as deptname
from employee as e
outer apply dbo.fun_splitids(e.deptids) as did
left join department as d on did.id=d.id
),mike as(
select id,name,deptids,deptname
,row_number()over(partition by id order by id) as level_num
from employet
),mike2 as(
select id,name,deptids,cast(deptname as nvarchar(100)) as deptname,level_num
from mike
where level_num=1
union all
select m.id,m.name,m.deptids,cast(m2.deptname+’,’+m.deptname as nvarchar(100)) as deptname,m.level_num
from mike as m
inner join mike2 as m2 on m.id=m2.id and m.level_num=m2.level_num+1
),maxmikebyidt as(
select id,max(level_num) as level_num
from mike2
group by id
)
select a.id,a.name,a.deptids,a.deptname
from mike2 as a
inner join maxmikebyidt as b on a.id=b.id and a.level_num=b.level_num
order by a.id option (maxrecursion 0)