日期:2014-05-17 浏览次数:20571 次
if not object_id('tb') is null drop table tb Go Create table tb([col1] nvarchar(1),[col2] nvarchar(1),[col3] nvarchar(1)) Insert tb select N'a',N'b',N'c' union all select N'a',N'b',N'f' union all select N'a',N'c',N'g' Go alter table tb add id int identity Go Select 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 */
------解决方案--------------------
--> 测试数据:[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 all select 'a','b','f' union all select '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
------解决方案--------------------
借4L数据: if not object_id('tb') is null drop table tb Go Create table tb([col1] nvarchar(1),[col2] nvarchar(1),[col3] nvarchar(1)) Insert tb select N'a',N'b',N'c' union all select N'a',N'b',N'f' union all select N'a',N'c',N'g' Go alter table tb add id int identity Go Select 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 行受影响) */