我有个表有以下数据:
a b c d
a,a tt,tt cc,dd ee,ee
b,b tt,cc cc,cc ff,gg
c,c aa,bb tt,tt aa,aa
数据是逗号分开的,我想把逗号左边和右边相同的数据只显示一次,并且把逗号去掉,左边和右边不相同的数据就保留原样显示,请问高手怎么实现?
------解决方案--------------------
- SQL code
create table tb( a varchar(5), b varchar(10), c varchar(10), d varchar(10))insert into tbselect 'a,a','tt,tt','cc,dd','ee,ee' union allselect 'b,b','tt,cc','cc,cc','ff,gg' union allselect 'c,c','aa,bb','tt,tt','aa,aa'select case when LEFT(a,CHARINDEX(',',a)-1)=right(a,len(a)-CHARINDEX(',',a)) then LEFT(a,CHARINDEX(',',a)-1) else a end as a, case when LEFT(b,CHARINDEX(',',b)-1)=right(b,len(b)-CHARINDEX(',',b)) then LEFT(b,CHARINDEX(',',b)-1) else b end as b, case when LEFT(c,CHARINDEX(',',c)-1)=right(c,len(c)-CHARINDEX(',',a)) then LEFT(c,CHARINDEX(',',c)-1) else c end as c, case when LEFT(d,CHARINDEX(',',d)-1)=right(d,len(d)-CHARINDEX(',',d)) then LEFT(d,CHARINDEX(',',d)-1) else d end as dfrom tb---------------a b c da tt cc,dd eeb tt,cc cc,cc ff,ggc aa,bb tt,tt aa
------解决方案--------------------
- Assembly code
create table #temp( a varchar(100), b varchar(100), c varchar(100), d varchar(100))insert into #tempselect 'a,a', 'tt,tt', 'cc,dd', 'ee,ee' union allselect 'b,b', 'tt,cc', 'cc,cc', 'ff,gg' union allselect 'c,c', 'aa,bb', 'tt,tt', 'aa,aa' select * from #tempSELECT a = CASE WHEN (SUBSTRING(a, 1, CHARINDEX(',', a)-1)) = (SUBSTRING(a, CHARINDEX(',', a)+1, LEN(a)-CHARINDEX(',', a))) THEN (SUBSTRING(a, 1, CHARINDEX(',', a)-1)) ELSE a END, b = CASE WHEN (SUBSTRING(b, 1, CHARINDEX(',', b)-1)) = (SUBSTRING(b, CHARINDEX(',', b)+1, LEN(b)-CHARINDEX(',', b))) THEN (SUBSTRING(b, 1, CHARINDEX(',', b)-1)) ELSE b END, c = CASE WHEN (SUBSTRING(c, 1, CHARINDEX(',', c)-1)) = (SUBSTRING(c, CHARINDEX(',', c)+1, LEN(c)-CHARINDEX(',', c))) THEN (SUBSTRING(c, 1, CHARINDEX(',', c)-1)) ELSE c END, d = CASE WHEN (SUBSTRING(d, 1, CHARINDEX(',', d)-1)) = (SUBSTRING(d, CHARINDEX(',', d)+1, LEN(d)-CHARINDEX(',', d))) THEN (SUBSTRING(d, 1, CHARINDEX(',', d)-1)) ELSE d ENDFROM #temp
------解决方案--------------------
更新：
- Assembly code
insert into #tempselect 'a', 'tt,tt', 'cc,dd', 'ee,ee' union allselect 'b', 'tt,cc', 'cc,cc', 'ff,gg' union allselect 'c', 'aa,bb', 'tt,tt', 'aa,aa' select * from #tempSELECT a = CASE WHEN (LEN(a)%2 = 1 AND (CHARINDEX(',', a) = (LEN(a)+1)/2) AND LEFT(a, (LEN(a)-1)/2) = RIGHT(a, (LEN(a)-1)/2)) THEN LEFT(a, (LEN(a)-1)/2) ELSE a END, b = CASE WHEN (LEN(b)%2 = 1 AND (CHARINDEX(',', b) = (LEN(b)+1)/2) AND LEFT(b, (LEN(b)-1)/2) = RIGHT(b, (LEN(b)-1)/2)) THEN LEFT(b, (LEN(b)-1)/2) ELSE b END, c = CASE WHEN (LEN(c)%2 = 1 AND (CHARINDEX(',', c) = (LEN(c)+1)/2) AND LEFT(c, (LEN(c)-1)/2) = RIGHT(c, (LEN(c)-1)/2)) THEN LEFT(c, (LEN(c)-1)/2) ELSE c END, d = CASE WHEN (LEN(d)%2 = 1 AND (CHARINDEX(',', d) = (LEN(d)+1)/2) AND LEFT(d, (LEN(d)-1)/2) = RIGHT(d, (LEN(d)-1)/2)) THEN LEFT(d, (LEN(d)-1)/2) ELSE d ENDFROM #temp
------解决方案--------------------
用函数吧
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([a] varchar(5),[b] varchar(8),[c] varchar(8),[d] varchar(8))insert [tb]select 'a,a','tt,tt','cc,dd','ee,ee' union allselect 'b,b','tt,cc','cc,cc','ff,gg' union allselect 'c','d','a','a' union allselect 'c,c','aa,bb','tt,tt','aa,aa' union allselect 'c','a','d','b' union allselect 'c,c,c','aa,bb,cc','tt,tt,tt','aa,aa,aa'gocreate function f_test(@s varchar(100))returns varchar(200)asbegin declare @rel varchar(200); declare @t table(a varchar(100)); set @[email protected]+',' while charindex(',',@s)>0 begin insert into @t select left(@s,charindex(',',@s)-1); set @s=stuff(@s,1,charindex(',',@s),''); end select @rel=isnull(@rel+',','')+a from (select distinct a from @t) t return @relendgoselect dbo.f_test(a) as a, dbo.f_test(b) as b, dbo.f_test(c) as c, dbo.f_test(d) as d from tb/**a tt cc,dd eeb cc,tt cc ff,ggc d a ac aa,bb tt aac a d bc aa,bb,cc tt aa**/drop function f_testdrop table tb