日期:2014-05-17  浏览次数:20530 次

如何利用sql语句实现类似powerbuilder中重复列值
如何利用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 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

*/

------解决方案--------------------
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 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

------解决方案--------------------
SQL code
借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 行受影响)
*/