不好意思,表达错误了。删除col列重复的记录,仅保存其中任意一条。 如:col1 col2 col3 1 as as 2 asa asa 2 asa aaa 结果:col1 col2 col3 1 as as 2 asa asa
delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
create table tablename as select distinct * from tablename
如何删除数据库中的重复记录 delete from test a where a.rowid!=(select max(rowid) from test b where a.a=b.a and a.b=b.b and a.c=b.c); delete from test where rowid not in(select max(rowid) from test group by a,b) --------------------------------------------- 在表中插入记录的时候,让主键自动产生——序列的应用 drop table foo; create table foo( id varchar2(20) primary key, data varchar2(100)); create sequence foo_seq; create or replace trigger bifer_foo_id_pk before insert on foo for each row begin select to_char(foo_seq.nextval) into :new.id from dual; end; / insert into foo(data) values('Chirstopher'); insert into foo(id,data) values(5,'Sean'); select * from foo; --------------------------------------------- 选出表中间某段——关于rownum select * from test where rownum < m+n minus select * from test where rownum > m --------------------------------------------- 多表连接随机抽取N行 select * from (select * from tablename order by dbms_random.value) where rownum< N; --------------------------------------------- 查系统日期格式 SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'; SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'; --------------------------------------------- 用SQL语句修改表中字段的名字 ALTER TABLE test RENAME COLUMN oldc TO newc;
方法原理: 1、Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的, rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中 那些具有最大rowid的就可以了,其余全部删除。 3、以下语句用到了3项技巧:rowid、子查询、别名。 实现方法: SQL> create table a ( 2 bm char(4), --编码 3 mc varchar2(20) --名称 4 ) 5 / 表已建立. SQL> insert into a values('1111','1111'); SQL> insert into a values('1112','1111'); SQL> insert into a values('1113','1111'); SQL> insert into a values('1114','1111'); SQL> insert into a select * from a; 插入4个记录. SQL> commit; 完全提交. SQL> select rowid,bm,mc from a; ROWID BM MC ------------------ ---- ------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111 查询到8记录. 查出重复记录 SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); ROWID BM MC ------------------ ---- -------------------- 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 删除重复记录 SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); 删除4个记录. SQL> select rowid,bm,mc from a; ROWID BM MC ------------------ ---- -------------------- 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111
例如:存在表 a1(id,mc) DELETE FROM a1 WHERE rowid NOT IN ( select max(a.rowid) from a1 a, a1 b where a.id = b.id and a.mc = b.mc group by a.id,a.mc ) ;
如:col1 col2 col3
1 as as
2 asa asa
2 asa aaa
结果:col1 col2 col3
1 as as
2 asa asa
a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
select distinct * from tablename
delete from test a where a.rowid!=(select max(rowid) from test b where a.a=b.a and a.b=b.b and a.c=b.c);
delete from test where rowid not in(select max(rowid) from test group by a,b)
---------------------------------------------
在表中插入记录的时候,让主键自动产生——序列的应用
drop table foo;
create table foo(
id varchar2(20) primary key,
data varchar2(100));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert
on foo
for each row
begin
select to_char(foo_seq.nextval) into :new.id from dual;
end;
/
insert into foo(data)
values('Chirstopher');
insert into foo(id,data)
values(5,'Sean');
select * from foo;
---------------------------------------------
选出表中间某段——关于rownum
select * from test where rownum < m+n
minus
select * from test where rownum > m
---------------------------------------------
多表连接随机抽取N行
select * from (select * from tablename order by dbms_random.value) where rownum< N;
---------------------------------------------
查系统日期格式
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
---------------------------------------------
用SQL语句修改表中字段的名字
ALTER TABLE test RENAME COLUMN oldc TO newc;
1、Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中
那些具有最大rowid的就可以了,其余全部删除。 3、以下语句用到了3项技巧:rowid、子查询、别名。 实现方法:
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 / 表已建立. SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111'); SQL> insert into a select * from a; 插入4个记录. SQL> commit; 完全提交. SQL> select rowid,bm,mc from a; ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111 查询到8记录. 查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111 删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); 删除4个记录. SQL> select rowid,bm,mc from a; ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
DELETE
FROM a1
WHERE rowid NOT IN ( select max(a.rowid)
from a1 a, a1 b
where a.id = b.id
and a.mc = b.mc
group by a.id,a.mc ) ;