日期:2014-05-17 浏览次数:20705 次
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 行受影响)
*/