合并问题,请大家指教~~谢谢
如下数据：
kid lid f3 f4 f5 
 
1 2 a NULL a
1 2 NULL e c
1 2 b b NULL
2 9 u x n
1 3 k f d


现要将kid和lid均相同的行进行合并
合并后的数据为： 
kid lid f3 f4 f5 
 
1 2 a b a
2 9 u x n
1 3 k f d


亦即是：kid和lid均相同的行,其最靠前的一行的某一字段，如果不为空，则保留该值，如果为空，刚取最后一行的值．
谢谢大家！

------解决方案--------------------
Select kid,lid,min(f3,'z') as f3,
min(f4,'z') as f4,min(f5,'z') as f5
from 表 group by kid,lid
------解决方案--------------------
select *,identity(int,1,1)id into #b from #a--#a为原始数据
------------------------------------------
select ta.kid,ta.lid,COALESCE(ta.f3,tb.f3)f3,COALESCE(ta.f4,tb.f4)f4,COALESCE(ta.f5,tb.f5)f5 from
(select b.* from (select kid,lid,min(id)id1 from #b group by kid,lid)a,#b b where a.id1=b.id)ta,
(select d.* from (select kid,lid,max(id)id1 from #b group by kid,lid)c,#b d where c.id1=d.id)tb
where ta.kid=tb.kid and ta.lid=tb.lid order by ta.id
-------------------------------------------
------解决方案--------------------
create table test(kid int,lid int,f3 varchar(8),f4 varchar(8),f5 varchar(8))
insert into test values(1,2,'a' ,NULL,'a' )
insert into test values(1,2,NULL,'e' ,'c' )
insert into test values(1,2,'b' ,'b' ,NULL) 
insert into test values(2,9,'u' ,'x' ,'n' )
insert into test values(1,3,'k' ,'f' ,'d' )
go

select identity(int,1,1) as id,* into # from test

select distinct
t.kid,t.lid,
f3=(select top 1 f3 from # where kid=t.kid and lid=t.lid and f3 is not null order by id),
f4=(select top 1 f4 from # where kid=t.kid and lid=t.lid and f4 is not null order by id),
f5=(select top 1 f5 from # where kid=t.kid and lid=t.lid and f5 is not null order by id)
from
# t
go

/*
kid lid f3 f4 f5
----------- ----------- -------- -------- -------- 
1 2 a e a
1 3 k f d
2 9 u x n
*/


drop table test,#
go


------解决方案--------------------
--原始数据：@T
declare @T table(kid int,lid int,f3 varchar(4),f4 varchar(4),f5 varchar(4))
insert @T
select null,null,null,null,null union all
select 1,2,'a',NULL,'a' union all
select 1,2,NULL,'e','c' union all
select 1,2,'b','b',NULL union all
select 2,9,'u','x','n' union all
select 1,3,'k','f','d'

/*
现要将kid和lid均相同的行进行合并
合并后的数据为： 
kid lid f3 f4 f5 
1 2 a b a
2 9 u x n
1 3 k f d
亦即是：kid和lid均相同的行,其最靠前的一行的某一字段，如果不为空，则保留该值，如果为空，刚取最后一行的值．
*/

--借助临时表#T
select id=identity(int,1,1),* into #T from @T

select a.kid,a.lid,f3=isnull(a.f3,b.f3),f4=isnull(a.f4,b.f4),f5=isnull(a.f5,b.f5)
from 
(select * from #T a where id=(select min(id) from #T where kid=a.kid and lid=a.lid)) a
join 
(select * from #T a where id=(select max(id) from #T where kid=a.kid and lid=a.lid)) b
on a.kid=b.kid and a.lid=b.lid

/*
得到结果
kid lid f3 f4 f5
1 2 a b a
1 3 k f d
2 9 u x n
*/

--删除测试
drop table #T
------解决方案--------------------
create table tb(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10))