CREATE TABLE [dbo].[Sw_users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_Sw_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
insert into Sw_users(username,name) values('zhangshan','张三')
insert into Sw_users(username,name) values('lisi','李四')
insert into Sw_users(username,name) values('wangwu','王五')
insert into Sw_users(username,name) values('liudehua','刘德华')
insert into Sw_users(username,name) values('zhangxueyou','张学友')
CREATE TABLE [dbo].[md_dept](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[director] [varchar](20) NULL,
CONSTRAINT [PK_2015_dept] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
inert into md_dept(name,director) values('市场部','zhangshan,lisi')
inert into md_dept(name,director) values('娱乐部','zhangxueyou,liudehua')
inert into md_dept(name,director) values('财务部','wangwu')
inert into md_dept(name,director) values('综合部','liudehua')
select a.*,b.name as pname from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0
我想要的格式是这样的：
name director panme
市场部 zhangshan,lisi 张三，李四
娱乐部 zhangxueyou,liudehua 张学友,刘德华
财务部 wangwu 王五
综合部 liudehua 刘德华
实际输出是这样的：
name director panme
市场部 zhangshan,lisi 张三
市场部 zhangshan,lisi 李四
娱乐部 zhangxueyou,liudehua 张学友
娱乐部 zhangxueyou,liudehua 刘德华
财务部 wangwu 王五
综合部 liudehua 刘德华
------解决思路----------------------
select a.*
,STUFF((SELECT ','+b.name FROM sw_users b WHERE CHARINDEX(','+b.username+',',','+a.director+',')>0 FOR XML PATH('')),1,1,'') as pname
from md_dept a where a.ID>0
不知道这样可不可以
------解决思路----------------------
with cte as (
select a.*,b.name as pname
from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0
)
select name,director,(stuff(select ','+pname from cte where name=c.name and director=c.director,1,1,'')) as pname
from cte as c group by name,director
------解决思路----------------------
你看看我的文章会对你的“便秘”有更好的疗效，开玩笑的，请看看我的博文，里面就是针对你这个问题写的
http://blog.csdn.net/dotnetstudio/article/details/17008693