表格单元格包含由逗号分隔的多个值，例如A，B，C，D，A，E，C，然后如何删除重复值

本文介绍了表格单元格包含由逗号分隔的多个值，例如A，B，C，D，A，E，C，然后如何删除重复值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

create table table_1（id int identity，name varchar（50）） 插入table_1值（'abc，jkm，pqr，abc，def，ghi '，jkm.ghi） 插入table_1值（'mno，pqr，abc，mno，pqr'） 插入table_1值（'abc，jkm，stu ，jkm，rcm，tin'） 我想删除重复的名称。 in id = 1 name is ='abc， jkm，pqr，abc，def，ghi，jkm，ghi' 现在删除重复名称后应为='abc，jkm，pqr，def，ghi'

create table table_1 (id int identity,name varchar(50)) insert into table_1 values ('abc,jkm,pqr,abc,def,ghi',jkm.ghi) insert into table_1 values ('mno,pqr,abc,mno,pqr') insert into table_1 values ('abc,jkm,stu,jkm,rcm,tin') I want to remove the duplicate name. in id=1 name is ='abc,jkm,pqr,abc,def,gjkm,ghi' now after remove the duplicate name should be='abc,jkm,pqr,def,ghi'

推荐答案

看看例子： Have a look at example: DECLARE @tmp VARCHAR(30) DECLARE @t TABLE (RowNo INT, MyValue VARCHAR(10), Remainder VARCHAR(30)) SET @tmp = 'A,B,C,D,A,E,C' ;WITH MyCTE AS ( SELECT 1 AS RowNo, LEFT(@tmp, CHARINDEX(',',@tmp)-1) AS MyValue, RIGHT(@tmp, LEN(@tmp) - CHARINDEX(',',@tmp)) AS Remainder WHERE CHARINDEX(',',@tmp)>0 UNION ALL SELECT RowNo +1 AS RowNo, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder FROM MyCTE WHERE CHARINDEX(',',Remainder)>0 UNION ALL SELECT RowNo +1 AS RowNo, Remainder AS MyValue, NULL AS Remainder FROM MyCTE WHERE CHARINDEX(',',Remainder)=0 ) INSERT INTO @t (RowNo, MyValue, Remainder) SELECT RowNo, MyValue, Remainder FROM MyCte --display MyCTE data SELECT * FROM @t --get duplicates SELECT MyValue, COUNT(MyValue) AS CountOfMyValue FROM @t GROUP BY MyValue HAVING COUNT(MyValue)>=1

在上面的例子中公用表表达式 [ ^ ]已用于将字段值拆分为一组记录。 您没有提供足够的信息来帮助您。请改进你的问题，我将保证更新我的答案，告诉你如何删除重复项（更新字段）。 我很高兴更新这个问题。最终解决方案是......

In above example Common Table Expression[^] has been used to split field values into set of records. You did not provide enough information to help you more. Please, improve your question and i'll promise to update my answer to show you how to remove duplicates (update field). I'm glad on update the question. The final solution is...

DECLARE @srctbl TABLE (id INT IDENTITY(1,1), names VARCHAR(50)) INSERT INTO @srctbl VALUES ('abc,jkm,pqr,abc,def,ghi,jkm,ghi'), ('mno,pqr,abc,mno,pqr'), ('abc,jkm,stu,jkm,rcm,tin') DECLARE @dsttbl TABLE (id INT, RowNo INT, MyValue VARCHAR(10), Remainder VARCHAR(30)) ;WITH MyCTE AS ( SELECT id, 1 AS RowNo, LEFT(names, CHARINDEX(',',names)-1) AS MyValue, RIGHT(names, LEN(names) - CHARINDEX(',',names)) AS Remainder FROM @srctbl WHERE CHARINDEX(',',names)>0 UNION ALL SELECT id, RowNo +1 AS RowNo, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder FROM MyCTE WHERE CHARINDEX(',',Remainder)>0 UNION ALL SELECT id, RowNo+1 AS RowNo, Remainder AS MyValue, NULL AS Remainder FROM MyCTE WHERE CHARINDEX(',',Remainder)=0 ) INSERT INTO @dsttbl (id, RowNo, MyValue, Remainder) SELECT id, RowNo, MyValue, Remainder FROM MyCte UPDATE t3 SET t3.names = t4.names FROM @srctbl AS t3 INNER JOIN ( SELECT t1.id, STUFF( (SELECT ',' + MyValue AS 'text()' FROM ( SELECT id, MyValue FROM @dsttbl GROUP BY id, MyValue HAVING COUNT(MyValue)>=1 ) AS t2 WHERE t2.id = t1.id FOR XML PATH('')), 1, 1, '') AS [names] FROM @dsttbl AS t1 GROUP BY t1.id ) AS t4 ON t3.id = t4.id SELECT * FROM @srctbl

[/ EDIT]

简单： 1）以字符串形式获取单元格内容。 2）将此字符串拆分为字符串数组（提示： String.Split方法 [ ^ ]）。 3）构造一个HashSet<string> [ ^ ]来自第2点获得的数组。 4）构造使用存储在HashSet中的值的最终字符串。 祝你好运。 Simple: 1) Get your cell content as a string. 2) Split this string into an array of strings (tip: String.Split Method[^]). 3) Construct a HashSet<string>[^] from the array obtained at point 2. 4) Construct your final string using values stored in HashSet. Good luck.