上一类似帖已结,现想要的结果有些变动,再次请教大家:)如下数据:
kid lid f3 f4 f5 f6
1 2 a NULL NULL NULL
1 2 NULL e NULL NULL
1 2 b NULL NULL e
1 2 q b NULL j
2 9 u x n b
1 3 k f d c现要将kid和lid均相同的行进行合并
合并后的数据为:
kid lid f3 f4 f5 f6
1 2 a e NULL e
2 9 u x n b
1 3 k f d c
亦即是kid,lid相同的,最靠前的一行的某一字段,
如果不为空,则保留该值;
如果为空,则有两种取值:一是都为空,则取值NULL;二是不全为空,则取最靠近的行且不为空的值.
kid lid f3 f4 f5 f6
1 2 a NULL NULL NULL
1 2 NULL e NULL NULL
1 2 b NULL NULL e
1 2 q b NULL j
2 9 u x n b
1 3 k f d c现要将kid和lid均相同的行进行合并
合并后的数据为:
kid lid f3 f4 f5 f6
1 2 a e NULL e
2 9 u x n b
1 3 k f d c
亦即是kid,lid相同的,最靠前的一行的某一字段,
如果不为空,则保留该值;
如果为空,则有两种取值:一是都为空,则取值NULL;二是不全为空,则取最靠近的行且不为空的值.
insert into A values(1,2, 'a' ,NULL, NULL,NULL )
insert into A values(1,2, NULL, 'e' , NULL,NULL )
insert into A values(1,2, 'b' , NULL ,NULL,'e')
insert into A values(1,2, 'q' , 'b' ,NULL, 'j' )
insert into A values(2,9, 'u' , 'x' ,'n', 'b' )
insert into A values(1,3, 'k' , 'f' , 'd','c')
go select identity(int,1,1) as id,* into #T from Aselect distinct
a.kid,a.lid,
f3=(select top 1 f3 from #T where kid=a.kid and lid=a.lid and f3 is not null order by id),
f4=(select top 1 f4 from #T where kid=a.kid and lid=a.lid and f4 is not null order by id),
f5=(select top 1 f5 from #T where kid=a.kid and lid=a.lid and f5 is not null order by id),
f6=(select top 1 f6 from #T where kid=a.kid and lid=a.lid and f6 is not null order by id)
from
#T a
go
drop table A,#T
insert into test values(1,2,'a' ,NULL,NULL,NULL)
insert into test values(1,2,NULL,'e' ,NULL,NULL)
insert into test values(1,2,'b' ,NULL,NULL,'e' )
insert into test values(1,2,'q' ,'b' ,NULL,'j' )
insert into test values(2,9,'u' ,'x' ,'n' ,'b' )
insert into test values(1,3,'k' ,'f' ,'d' ,'c' )
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),
f6=(select top 1 f6 from # where kid=t.kid and lid=t.lid and f6 is not null order by id)
from
# t
go /*
kid lid f3 f4 f5 f6
----------- ----------- -------- -------- -------- --------
1 2 a e NULL e
1 3 k f d c
2 9 u x n b
*/
drop table test,#
go
insert into tb values(1 , 2 , 'a' , NULL , NULL, NULL)
insert into tb values(1 , 2 , NULL, 'e' , NULL, NULL)
insert into tb values(1 , 2 , 'b' , NULL , NULL, 'e')
insert into tb values(1 , 2 , 'q' , 'b' , NULL, 'j')
insert into tb values(2 , 9 , 'u' , 'x' , 'n' , 'b')
insert into tb values(1 , 3 , 'k' , 'f' , 'd' , 'c')
go
select kid,lid , min(f3) f3 , min(f4) f4 , min(f5) f5 ,min(f6) f6 from
(
select kid,lid , f3 = (select top 1 f3 from tb where f3 is not null and kid = t.kid and lid = t.lid)
, f4 = (select top 1 f4 from tb where f4 is not null and kid = t.kid and lid = t.lid)
, f5 = (select top 1 f5 from tb where f5 is not null and kid = t.kid and lid = t.lid)
, f6 = (select top 1 f6 from tb where f6 is not null and kid = t.kid and lid = t.lid)
from tb t
) m
group by kid,lid drop table tb/*
kid lid f3 f4 f5 f6
----------- ----------- ---------- ---------- ---------- ----------
1 2 a e NULL e
1 3 k f d c
2 9 u x n b
*/
create table T
(
kid int,
lid int,
f3 nvarchar(1),
f4 nvarchar(1),
f5 nvarchar(1),
f6 nvarchar(1)
) insert T select 1, 2, 'a', NULL, NULL, NULL
union all select 1, 2, NULL, 'e', NULL, NULL
union all select 1, 2, 'b', NULL, NULL, 'e'
union all select 1, 2, 'q', 'b', NULL, 'j'
union all select 2, 9, 'u', 'x', 'n', 'b'
union all select 1, 3, 'k', 'f', 'd', 'c' select distinct kid,lid,
f3=(select top 1 f3 from T where kid=tmp.kid and lid=tmp.lid and f3 is not null),
f4=(select top 1 f4 from T where kid=tmp.kid and lid=tmp.lid and f4 is not null),
f5=(select top 1 f5 from T where kid=tmp.kid and lid=tmp.lid and f5 is not null),
f6=(select top 1 f6 from T where kid=tmp.kid and lid=tmp.lid and f6 is not null)
from T as tmp
--resultkid lid f3 f4 f5 f6
----------- ----------- ---- ---- ---- ----
1 2 a e NULL e
1 3 k f d c
2 9 u x n b(所影响的行数为 3 行)
有没有哪位能帮做个cursor的实现?
有没有哪位能帮做个cursor的实现?
---------------
星星钻钻们的代码已经满足你的要求了,你还要游标干嘛?
我这边select top ..这语法不能用啊,怎么办?
再次麻烦各位大哥了..
create table tb(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10),f6 varchar(10))
insert into tb values(1 , 2 , 'a ' , NULL , NULL, NULL)
insert into tb values(1 , 2 , NULL, 'e ' , NULL, NULL)
insert into tb values(1 , 2 , 'b ' , NULL , NULL, 'e ')
insert into tb values(1 , 2 , 'q ' , 'b ' , NULL, 'j ')
insert into tb values(2 , 9 , 'u ' , 'x ' , 'n ' , 'b ')
insert into tb values(1 , 3 , 'k ' , 'f ' , 'd ' , 'c ')
go
select kid,lid , min(f3) f3 , min(f4) f4 , min(f5) f5 ,min(f6) f6 from
(
select kid,lid , f3 = (select top 1 f3 from tb where f3 is not null and kid = t.kid and lid = t.lid)
, f4 = (select top 1 f4 from tb where f4 is not null and kid = t.kid and lid = t.lid)
, f5 = (select top 1 f5 from tb where f5 is not null and kid = t.kid and lid = t.lid)
, f6 = (select top 1 f6 from tb where f6 is not null and kid = t.kid and lid = t.lid)
from tb t
) m
group by kid,lid drop table tb /*
kid lid f3 f4 f5 f6
----------- ----------- ---------- ---------- ---------- ----------
1 2 a e NULL e
1 3 k f d c
2 9 u x n b
*/
我这边不能用TOP啊,其它实现帮帮忙
insert into # values(1 , 2 , 'a ' , NULL , NULL, NULL)
insert into # values(1 , 2 , NULL, 'e ' , NULL, NULL)
insert into # values(1 , 2 , 'b ' , NULL , NULL, 'e ')
insert into # values(1 , 2 , 'q ' , 'b ' , NULL, 'j ')
insert into # values(2 , 9 , 'u ' , 'x ' , 'n ' , 'b ')
insert into # values(1 , 3 , 'k ' , 'f ' , 'd ' , 'c ')
go select kid,kid,min(f3),min(f4),min(f5),min(f6)
from #
group by kid,lid