我有个表
create table ERROR
(
NAME NUMBER,
BCSJ NUMBER,
ZKBH NUMBER
)
数据如下:
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 6, 17);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 4, 21);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, 7, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, 3);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 1, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 1, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, 1, 11);
commit;
我要给group by name 给 bcsj编号 已经编号的不变为空的 group by name 编号 这个序号不能超过 某个NAME下记录的总数name,bcsj 联合唯一
也就是说
name bcsj
4 1
4
4
4
bcsj 只能是 2,3,4 但是具体的 放哪个位置无所谓
create table ERROR
(
NAME NUMBER,
BCSJ NUMBER,
ZKBH NUMBER
)
数据如下:
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 6, 17);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 4, 21);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, 7, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, 3);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (1, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (5, 1, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 2, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (3, 1, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, null, null);
insert into ERROR (NAME, BCSJ, ZKBH)
values (2, 1, 11);
commit;
我要给group by name 给 bcsj编号 已经编号的不变为空的 group by name 编号 这个序号不能超过 某个NAME下记录的总数name,bcsj 联合唯一
也就是说
name bcsj
4 1
4
4
4
bcsj 只能是 2,3,4 但是具体的 放哪个位置无所谓
解决方案 »
- 指定定长数据文件换行问题
- 删除表时候可不可以不删除与该表关联的触发器?
- 难道此题无解么?关于用户权限控制的问题
- 请教一个数据库中数据并发的问题?高分!
- 2进制文件插入Long Raw类型的字段,为什么报"数据大小超出此类型的最大值"错误?
- oracle 建表时 提示 SP2-0640: 未连接 在线等..
- 如果写遍历时间的SELECT语句?
- oracle 如何删除 不必要的数据文件
- Oracle817在2000Server上的安装问题!
- 关于监听器的问题?
- 为什么我从SQL 2005导出一个表到oracle 10g,在查询的时候提示我 oracle table or view does not exist
- 靠完OCA的007以及042.但是043比较难,如何准备啊?
create table GK
(
GKBH NUMBER,
GKXLH NUMBER,
ZKBH NUMBER
);
create table ERROR_B
(
NAME NUMBER,
BCSJ NUMBER,
ZKBH NUMBER
);
create or replace procedure pro_bczk
is
v_sql string(2000);
begin
for v_count in 1..7 loop
v_sql:='
insert into gk select * from error where (name,bcsj) in
(select name,c from (
select name,count(*) c,max(bcsj)m from error group by name)
where c=m)';
execute immediate v_sql;
COMMIT;
delete from error where (name,bcsj) in
(select name,c from (
select name,count(*) c,max(bcsj)m from error group by name)
where c=m);
COMMIT;
v_sql:='
create table er as select name,c from (
select name,count(*) c,max(bcsj)m from error group by name)';
execute immediate v_sql;insert into error_b select * from error where bcsj is not null;
COMMIT;
delete from error where bcsj is not null;
COMMIT;
v_sql:='
create table err as
select name,row_number()over(partition by
name order by name) rm from error where bcsj
is null';
execute immediate v_sql;
v_sql:='
update err set rm=null where rm!=1';
execute immediate v_sql;
v_sql:='
update err set rm=(select c from er where err.name=er.name and rm=1)';
execute immediate v_sql;
commit;
v_sql:='
TRUNCATE TABLE ERROR';
execute immediate v_sql;
INSERT INTO ERROR SELECT * FROM ERROR_B;
v_sql:='
INSERT INTO ERROR(name,Bcsj) SELECT * FROM ERR WHERE RM IS NULL';
execute immediate v_sql;
COMMIT;
v_sql:='
insert into gk(gkbh,gkxlh) select * from err where rm is not null';
execute immediate v_sql;
commit;
v_sql:='
DROP TABLE ER';
execute immediate v_sql;
v_sql:='
DROP TABLE ERR';
execute immediate v_sql;
v_sql:='
TRUNCATE TABLE ERROR_B';
execute immediate v_sql;
end loop;
end pro_bczk;创建2个辅助的表 执行下储存过程就可以了 上面的 7 需要 根据
下面的查询结果得到:
select max(t) from (
select count(*) t,name from gk t group by name);gk表的数据就是转换后的真实数据
有没简单的方法 忘赐教
create or replace procedure pro_bczk
is
v_name number;
v_count number;
i number;
j number;
cursor v_cur is select name,count(*) from ERROR group by name;
begin
open v_cur;
fetch v_cur into v_name,v_count;
while v_cur %found loop
for i in 1..v_count loop
select count(*) into j from error where name=v_name and bcsj=v_count;
if j=0 then
update error set bcsj=v_count where name=v_name and rownum=1;
commit;
end if;
end loop;
fetch v_cur into v_name,v_count;
end loop;
end;
is
v_name number;
v_count number;
i number;
j number;
cursor v_cur is select name,count(*) from ERROR group by name;
begin
open v_cur;
fetch v_cur into v_name,v_count;
while v_cur %found loop
for i in 1..v_count loop
select count(*) into j from error where name=v_name and bcsj=i;
if j=0 then
update error set bcsj=i where name=v_name and rownum=1 and bcsj is null;
end if;
end loop;
fetch v_cur into v_name,v_count;
end loop;
close v_cur;
commit;
end;