一个表user user表中含有user_id 和 user_name 两个字段
一个表meeting 表中 含有m_id,m_attendee字段
user表示例(user_id,user_name)
user_id user_name
00001 张三
00002 李四
00003 王五
00004 赵大
meeting表示例(m_id,m_attendee)
m_id m_attendee
01 00001,00002,00003
02 00003,00004
03 00001,00004
如何用sql语句查询出以下结果
例如
m_id m_attendee
01 张三,李四,王五
02 王五,赵大
03 张三,赵大

写了个sqlserver版本的
建表及初始数据
CREATE TABLE [user] (
[user_id] VARCHAR(50) NOT NULL,
[user_name] VARCHAR(50),
CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED ([user_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
CREATE TABLE meeting (
m_id VARCHAR(50) NOT NULL,
m_attendee VARCHAR(4000),
CONSTRAINT [PK_meeting] PRIMARY KEY CLUSTERED ([m_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
INSERT INTO [user]([user_id],[user_name])
VALUES('00001 ','张三')
INSERT INTO [user]([user_id],[user_name])
VALUES('00002 ','李四')
INSERT INTO [user]([user_id],[user_name])
VALUES('00003 ','王五')
INSERT INTO [user]([user_id],[user_name])
VALUES('00004 ','赵大')
INSERT INTO meeting(m_id,m_attendee)
VALUES('01 ','00001,00002,00003')
INSERT INTO meeting(m_id,m_attendee)
VALUES('02 ','00003,00004')
INSERT INTO meeting(m_id,m_attendee)
VALUES('03 ','00001,00004')
先创建一个分割函数
CREATE FUNCTION f_split(@璐村惂鐢ㄦ埛_00000eV馃惥 VARCHAR(4000),@split VARCHAR(2))
RETURNS @t😇 TABLE(id INT,col VARCHAR(4000))
AS
BEGIN
DECLARE @num INT
SET @num = 0
WHILE(charindex(@split,@c)<>0)
BEGIN
SET @num = @num + 1
INSERT @t(id,col) VALUES (@num,SUBSTRING(@c,1,CHARINDEX(@split,@c)-1))
SET @c = STUFF(@c,1,CHARINDEX(@split,@c),'')
IF @c = 'NULL' SET @c = null
END
SET @num = @num + 1
INSERT @t(id,col) VALUES (@num,@c)
RETURN
END
查询语句如下
CREATE TABLE #T(m_id VARCHAR(50),[user_id] VARCHAR(50))--创建临时表，用来存放会议与人员的关系
/*游标遍历会议表，利用分割函数将关系写入中间表*/
DECLARE @id VARCHAR(50)
DECLARE FirstCursor CURSOR LOCAL SCROLL FOR
SELECT m_id FROM meeting
OPEN FirstCursor
FETCH NEXT FROM FirstCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #T(m_id,[user_id] ) SELECT @id,col FROM dbo.f_split((SELECT m_attendee FROM meeting WHERE m_id = @id),',')
FETCH NEXT FROM FirstCursor INTO @id
END
CLOSE FirstCursor
DEALLOCATE FirstCursor
/*最终查询语句，利用STUFF函数连接字符串*/
SELECT T.m_id,STUFF((SELECT ','+B.[user_name] FROM #T T1 LEFT JOIN [user] B ON T1.[user_id] = B.[user_id] WHERE T1.m_id = T.m_id FOR XML PATH('')),1,1,'')
FROM #T T
LEFT JOIN [user] A ON T.[user_id] = A.[user_id]
GROUP BY T.m_id

select m_id
, ( SELECT STUFF(( SELECT ',' + user_name FROM #user
WHERE a.m_attendee like '%'+user_id+'%' FOR XML PATH('') ), 1, 1, '') ) m_attendee
from #meeting a
我用的是临时表，记得把#去掉