27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
pid AS [员工ID]
,STUFF(
(SELECT ','+ b.dname FROM b
WHERE a.dids LIKE '%'+b.did+'%' FOR XML PATH(''))
,1,1,'') AS [部门名称]
,pname AS [姓名]
,score AS [考分]
FROM a
USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
GO
CREATE TABLE a(
pid VARCHAR(10),
dids VARCHAR(50),
pname NVARCHAR(20),
score INT
)
CREATE TABLE b(
did VARCHAR(50),
dname NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO a
SELECT 'N001','D001','张三','90'
UNION ALL SELECT 'N002','D002,D003','刘杰','90'
UNION ALL SELECT 'N001','D001','王五','90'
UNION ALL SELECT 'N002','D002,D003','刘春','90'
INSERT INTO b
SELECT 'D001','技术部'
UNION ALL SELECT 'D002','采购部'
UNION ALL SELECT 'D003','销售部'
SELECT
pid AS [员工ID]
,STUFF(
(SELECT ','+ b.dname FROM b
WHERE ','+a.dids+',' LIKE '%,'+b.did+',%' FOR XML PATH(''))
,1,1,'') AS [部门名称]
,pname AS [姓名]
,score AS [考分]
FROM a
SELECT ','+ b.dname FROM b
WHERE ','+a.dids+',' LIKE '%,'+b.did+',%' FOR XML PATH(''))
,1,1,'') AS [部门名称]