合并问题,再次请教各位:)
上一类似帖已结,现想要的结果有些变动,再次请教大家:)
如下数据：
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;二是不全为空,则取最靠近的行且不为空的值．
[解决办法]
create table A(kid int,lid int,f3 varchar(10),f4 varchar(10),f5 varchar(10),f6 varchar(10))
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 A
select 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
[解决办法]
create table test(kid int,lid int,f3 varchar(8),f4 varchar(8),f5 varchar(8),f6 varchar(8))
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
[解决办法]
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
*/
[解决办法]
- SQL code
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