select min(id),(case when name1=null then (select name1 from link where number=a.number )
else name1 end)as name1,(case when name2=null then (select name2 from link where number=a.number) else name2 end)as name2 from link a
else name1 end)as name1,(case when name2=null then (select name2 from link where number=a.number) else name2 end)as name2 from link a
(id int identity(1,1) primary key,
name1 varchar(20),
name2 varchar(20),
number varchar(20)
)insert tbl select '刚铁柔','','011100'
union all select '','小刚','011100'
union all select '天下无双','','022200'
union all select '兰梅','','033300'
union all select '','小天','022200'
union all select '','阿兰','033300'alter function joinname1(@number varchar(10))
returns varchar(40)
as
begin
declare @str varchar(40)
set @str=''
select @str=@str+name1 from tbl where number=@number
return @str
endalter function joinname2(@number varchar(10))
returns varchar(40)
as
begin
declare @str varchar(40)
set @str=''
select @str=@str+name2 from tbl where number=@number
return @str
endselect min(id),dbo.joinname1(number) as name1,dbo.joinname2(number) as name2
from tbl group by number
http://community.csdn.net/Expert/TopicView3.asp?id=3998571
3个贴子,139分。up有分
select 1, '刚铁柔', '', '011100' union all
select 2, '' , '小刚', '011100' union all
select 3, '天下无双', '', '022200' union all
select 4, '兰梅', '', '033300' union all
select 5, '', '小天', '022200' union all
select 6, '', '阿兰' , '033300' union all
select 7, 'aaaaaa', 'bbbbb', '044400'update Link set Name1 = hexName1, Name2 = hexName2 from(
select min(id) as id, max(Name1) as hexName1, max(Name2) as hexName2 from Link group by Number having count(*) > 1 --得到你所说的三条合并后纪录
) L where Link.id = L.iddelete L from Link L where exists (select * from Link where Number = L.Number and id < L.id) --删除另外三条没有用的纪录--查看结果
select * from Link
将表分成两部分:
一、已经处理过的数据 ((name1 && name2) is not null) T1
二、未处理地数据((name1 || name2) is not null) T2
三、对T2 group by 过滤并与T1 union即可Sql如下:
Select * into ##t1 from tb2 where name1 is null or name2 is null
Select 'id'=min(id),'name1'=max(name1),'name2'=max(name2),number from ##t1 group by number
union
Select * from tb2 where name1 is not null and name2 is not null