如何利用sql语句实现类似powerbuilder中重复列值。
如a,b,c
a,b,f
a,c,g
三行查询出来显示
a,b,c
f
c,g
也就是一列的重复值只显示一次？
语句越精简越好。
------解决方案--------------------
- SQL code
if not object_id('tb') is null drop table tbGoCreate table tb([col1] nvarchar(1),[col2] nvarchar(1),[col3] nvarchar(1))Insert tbselect N'a',N'b',N'c' union allselect N'a',N'b',N'f' union allselect N'a',N'c',N'g'Goalter table tbadd id int identityGoSelect col1=case when exists(select 1 from tb where col1=t.col1 and id<t.id) then '' else col1 end, col2=case when exists(select 1 from tb where col2=t.col2 and id<t.id) then '' else col2 end , col3=case when exists(select 1 from tb where col3=t.col3 and id<t.id) then '' else col3 end from tb t/*col1 col2 col3---- ---- ----a b c f c g*/
------解决方案--------------------
- SQL code
--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(1))insert [tb]select 'a','b','c' union allselect 'a','b','f' union allselect 'a','c','g'select * from [tb];WITH TT as(SELECT col1,col2,col3,[num]=ROW_NUMBER() OVER (ORDER BY col1) FROM tb )Select col1=case when exists(select 1 from TT where col1=t.col1 and [num]<t.[num]) then '' else col1 end, col2=case when exists(select 1 from TT where col2=t.col2 and [num]<t.[num]) then '' else col2 end, col3=case when exists(select 1 from TT where col3=t.col3 and [num]<t.[num]) then '' else col3 end from TT t
------解决方案--------------------
- SQL code
借4L数据：if not object_id('tb') is null drop table tbGoCreate table tb([col1] nvarchar(1),[col2] nvarchar(1),[col3] nvarchar(1))Insert tbselect N'a',N'b',N'c' union allselect N'a',N'b',N'f' union allselect N'a',N'c',N'g'Goalter table tbadd id int identityGoSelect col1=case when id=(select top 1 id from tb where col1=t.col1) then col1 else '' end, col2=case when id=(select top 1 id from tb where col2=t.col2) then col2 else '' end , col3=case when id=(select top 1 id from tb where col3=t.col3) then col3 else '' end from tb t/*col1 col2 col3---- ---- ----a b c f c g(3 行受影响)*/