/*
应用场景:将项目组的员工职位合并到同一列
*/
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A
(
ID INT,
NAME VARCHAR(50)
)
GO
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B
(
NAME VARCHAR(20)
)
GO
INSERT INTO A
SELECT 1,'张三,李四'
UNION ALL
SELECT 2,'王五'
UNION ALL
SELECT 3,'马六,朱七'
GO
INSERT INTO B
SELECT '张三'
UNION
SELECT '朱七'
GO
SELECT ID,C.NAME,GADRES=(CASE WHEN NAME1=B.NAME AND NAME1 IS NOT NULL THEN '1'
WHEN B.NAME IS NULL THEN '' ELSE '0' END)
+(CASE WHEN B.NAME IS NULL THEN '' ELSE ',' END)
+(CASE WHEN NAME2=B.NAME THEN '1' ELSE '0' END)
FROM
(
SELECT ID,NAME,NAME1=SUBSTRING(NAME,0,CHARINDEX(',',NAME))
,NAME2=SUBSTRING(NAME,CHARINDEX(',',NAME)+1,LEN(NAME)-CHARINDEX(',',NAME)+1)
FROM A
) C
LEFT JOIN B
ON C.NAME1=B.NAME OR C.NAME2=B.NAME
GO
应用场景:将项目组的员工职位合并到同一列
最新推荐文章于 2024-06-24 17:22:29 发布