合并问题,请大家指教~~谢谢

[DBA_DREAM]

如下数据：
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均相同的行,其最靠前的一行的某一字段，如果不为空，则保留该值，如果为空，刚取最后一行的值．
谢谢大家！

[dawugui]

create table tb(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10))
insert into tb values(1, 2, 'a' , NULL, 'a' )
insert into tb values(1, 2, NULL , 'e' , 'c' )
insert into tb values(1, 2, 'b' , 'b' , NULL)
insert into tb values(2, 9, 'u' , 'x' , 'n' )
insert into tb values(1, 3, 'k' , 'f' , 'd' )
go
--借助一临时表实现
select id=identity(int,1,1) , * into tmp from tb

select m.kid , m.lid , isnull(m.f3,n.f3) f3,isnull(m.f4,n.f4) f4 , isnull(m.f5,n.f5) f5 from
(
select a.* from tmp a,
(select kid,lid,min(id) id from tmp group by kid,lid) b
where a.kid = b.kid and a.lid = b.lid and a.id = b.id
) m
left join
(
select a.* from tmp a,
(select kid,lid,max(id) id from tmp group by kid,lid) b
where a.kid = b.kid and a.lid = b.lid and a.id = b.id
) n
on m.kid = n.kid and m.lid = n.lid

drop table tb,tmp


/*
kid lid f3 f4 f5
----------- ----------- ---------- ---------- ----------
1 2 a b a
1 3 k f d
2 9 u x n
（所影响的行数为 3 行）
*/

[DBA_DREAM]

dfdfdfd

[Limpire]

变成代码吧：

--原始数据：@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'

--借助临时表#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

[WangZWang]

Select kid,lid,min(f3,'z') as f3,
min(f4,'z') as f4,min(f5,'z') as f5
from 表 group by kid,lid

[DBA_DREAM]

谢谢楼上的．
但是f3,f4,f5主几个字段有可能不是字符类型的．可以考虑更全面点吗

[easonjun]

表的主键是什么？
是不是要用游标

[DBA_DREAM]

可以用游标,只要能实现就行.

[销魂的拖拉机]

:)

[wgzaaa]

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

[Limpire]

--原始数据：@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

[DBA_DREAM]

thanks~

[easonjun]

星星就是猛，学习了