SELECT t.xh, t.deptid, t.zwid, nvl(names, (SELECT names FROM testuser a WHERE a.deptid = t.deptid AND a.zwid = t.zwid AND a.names IS NOT NULL AND rownum < 2)) names FROM testuser t;
create table testuser( XH numeric NOT NULL ,-----该字段值是唯一的; deptid numeric NOT NULL , zwid numeric NULL , names varchar(20) NULL, constraint testuser_pk PRIMARY KEY (XH) ); ------测试数据: insert into testuser(XH,deptid,zwid,names) values(1,1,2,'a1'); insert into testuser(XH,deptid,zwid,names) values(2,1,2,'a1'); insert into testuser(XH,deptid,zwid,names) values(3,1,3,'a3'); insert into testuser(XH,deptid,zwid,names) values(4,1,2,''); insert into testuser(XH,deptid,zwid,names) values(5,2,4,'a4'); insert into testuser(XH,deptid,zwid,names) values(6,2,6,'a4'); insert into testuser(XH,deptid,zwid,names) values(7,2,10,'a5'); insert into testuser(XH,deptid,zwid,names) values(8,3,10,''); insert into testuser(XH,deptid,zwid,names) values(9,2,10,''); insert into testuser(XH,deptid,zwid,names) values(10,3,10,'a7'); select a.xh,a.deptid,a.zwid,case when names is null then (select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid and rownum=1 ) else a.names end from testuser a where names is null
create table testuser( XH numeric NOT NULL ,-----该字段值是唯一的; deptid numeric NOT NULL , zwid numeric NULL , names varchar(20) NULL, constraint testuser_pk PRIMARY KEY (XH) ); ------测试数据: insert into testuser(XH,deptid,zwid,names) values(1,1,2,'a1'); insert into testuser(XH,deptid,zwid,names) values(2,1,2,'a1'); insert into testuser(XH,deptid,zwid,names) values(3,1,3,'a3'); insert into testuser(XH,deptid,zwid,names) values(4,1,2,''); insert into testuser(XH,deptid,zwid,names) values(5,2,4,'a4'); insert into testuser(XH,deptid,zwid,names) values(6,2,6,'a4'); insert into testuser(XH,deptid,zwid,names) values(7,2,10,'a5'); insert into testuser(XH,deptid,zwid,names) values(8,3,10,''); insert into testuser(XH,deptid,zwid,names) values(9,2,10,''); insert into testuser(XH,deptid,zwid,names) values(10,3,10,'a7'); --查询语句 select a.xh,a.deptid,a.zwid,case when names is null then (select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid and b.names is not null and rownum=1 ) else a.names end from testuser a;--修改语句 update testuser a set names=(select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid and b.names is not null and rownum=1) where names is null ;select * from testuser;
t.deptid,
t.zwid,
nvl(names,
(SELECT names
FROM testuser a
WHERE a.deptid = t.deptid AND
a.zwid = t.zwid AND
a.names IS NOT NULL AND
rownum < 2)) names
FROM testuser t;
create table testuser(
XH numeric NOT NULL ,-----该字段值是唯一的;
deptid numeric NOT NULL ,
zwid numeric NULL ,
names varchar(20) NULL,
constraint testuser_pk PRIMARY KEY (XH)
);
------测试数据:
insert into testuser(XH,deptid,zwid,names)
values(1,1,2,'a1');
insert into testuser(XH,deptid,zwid,names)
values(2,1,2,'a1');
insert into testuser(XH,deptid,zwid,names)
values(3,1,3,'a3');
insert into testuser(XH,deptid,zwid,names)
values(4,1,2,'');
insert into testuser(XH,deptid,zwid,names)
values(5,2,4,'a4');
insert into testuser(XH,deptid,zwid,names)
values(6,2,6,'a4');
insert into testuser(XH,deptid,zwid,names)
values(7,2,10,'a5');
insert into testuser(XH,deptid,zwid,names)
values(8,3,10,'');
insert into testuser(XH,deptid,zwid,names)
values(9,2,10,'');
insert into testuser(XH,deptid,zwid,names)
values(10,3,10,'a7'); select a.xh,a.deptid,a.zwid,case when names is null then
(select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid and rownum=1
) else a.names end from testuser a
where names is null
create table testuser(
XH numeric NOT NULL ,-----该字段值是唯一的;
deptid numeric NOT NULL ,
zwid numeric NULL ,
names varchar(20) NULL,
constraint testuser_pk PRIMARY KEY (XH)
);
------测试数据:
insert into testuser(XH,deptid,zwid,names)
values(1,1,2,'a1');
insert into testuser(XH,deptid,zwid,names)
values(2,1,2,'a1');
insert into testuser(XH,deptid,zwid,names)
values(3,1,3,'a3');
insert into testuser(XH,deptid,zwid,names)
values(4,1,2,'');
insert into testuser(XH,deptid,zwid,names)
values(5,2,4,'a4');
insert into testuser(XH,deptid,zwid,names)
values(6,2,6,'a4');
insert into testuser(XH,deptid,zwid,names)
values(7,2,10,'a5');
insert into testuser(XH,deptid,zwid,names)
values(8,3,10,'');
insert into testuser(XH,deptid,zwid,names)
values(9,2,10,'');
insert into testuser(XH,deptid,zwid,names)
values(10,3,10,'a7'); --查询语句
select a.xh,a.deptid,a.zwid,case when names is null then
(select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid
and b.names is not null and rownum=1
) else a.names end from testuser a;--修改语句
update testuser a set names=(select names from testuser b where a.deptid=b.deptid and a.zwid=b.zwid
and b.names is not null and rownum=1)
where names is null ;select * from testuser;