原来一直弄sqlserver的 ,对oracle的sql 不懂 ,大家能给解释下 面 这两个sql 什么意思吗 139. 如何查找重复记录?
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
140. 如何删除重复记录?
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
141. 如何快速编译所有视图?
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
140. 如何删除重复记录?
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
141. 如何快速编译所有视图?
每一行记录都有一个rowid,并且唯一,它相当于是这行记录的物理地址。根据其唯一特性,在子查询中取具有相同列值,但最大的rowid,
在外层中判断相同列值和最大rowid不相等即为重复记录
SQL> create table table_name(col1 number,col2 number);
Table created
SQL> insert into table_name values(1,1);
1 row inserted
SQL> insert into table_name values(1,1);
1 row inserted
SQL> insert into table_name values(1,1);
1 row inserted
SQL> insert into table_name values(2,2);
1 row inserted
SQL> insert into table_name values(2,2);
1 row insertedSQL>--查询记录
SQL> select rowid,col1,col2 from table_name;
ROWID COL1 COL2
------------------ ---------- ----------
AAATV6AAEAAAAtIAAA 1 1
AAATV6AAEAAAAtIAAB 1 1
AAATV6AAEAAAAtIAAC 1 1
AAATV6AAEAAAAtIAAD 2 2
AAATV6AAEAAAAtIAAE 2 2
SQL>--提取所有重复的记录
SQL> SELECT * FROM TABLE_NAME
2 WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
3 WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
COL1 COL2
---------- ----------
1 1
1 1
2 2
SQL> --删除重复的记录
SQL> DELETE FROM TABLE_NAME
2 WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
3 WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
3 rows deleted
SQL> select * from table_name;
COL1 COL2
---------- ----------
1 1
2 2
SQL>
它表示数据在系统中的地址或存储位置,它是实际存在的,
下面是rowid表示的具体意思:
AAATV6 AAE AAAAtI AAA
数据库段 数据文件号 数据块号 块行号
oracle rowid