删除重复的记录(30分)

软件环境：
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为：C:/ORANT

问题提出：
1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。
方法原理：
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

有几个问题搞不清，请大家教教我
1 语句中select max(rowid) from a b
那个b 代表什么，有什么用？
2 子查询select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc
的查询结果是什么？
为什么单独使用这个查询会出错
ERROR 位于第 1 行:
ORA-00904: 无效列名
刚学SQL 请高手指教。

->那个b 代表什么，有什么用？
这个b是数据表a的一个别名，其实就是指表a。子查询和外部查询引用同一表的语句可被表述为自联
接（将某个表与自身联接）。由于自联接的表会以两种不同的角色出现，所以必须有表别名
->查询结果是什么？
在表a与b中查找出bm与mc都相同而rowid最大的记录,由于a与b其实是同一个表，所以找出的是表a中
重复记录(bm,mc)的rowid最大值的记录。
->为什么单独使用这个查询会出错
select max([red]rowid[/red]) from a b where a.bm=b.bm and a.mc=b.mc
由于有a与表b中都有rowid这个字段，所以sql不知道你要取哪个表中的就出错了,另外在包括相关子
查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，
为外部查询可能选择的每一行均执行一次。

感谢windbell！，可是我的分就这些了。

分不在多少！解决问题就好！[]

那么如果单独使用
select * from a b where a.bm=b.bm and a.mc=b.mc
为什么也出错呢？是不是同样不知道需要取哪个表？

那样单独写没有什么意义，不过你非要那样的话可以改为如下：
　　select * from a b where bm=b.bm and mc=b.mc

在sql中解释如下：
指定了未对应于查询中指定的任何表名的列前缀。将列前缀与 FROM 子句中的表名和别名相匹配。
该错误的一个常见起因是当提供了表的别名时使用表名。当使用表别名（ANSI 术语中的相关名称）时，Microsoft® SQL Server™ 中的语法检查遵照 ANSI 规范。ANSI 指出

A <table name> ... is exposed ... if and only if the <table reference>
does not specify a <correlation name>.

如果已在 FROM 子句中为表名提供了别名，则只可以使用别名来限定表中的列；表名不能在语句中的其它任何位置使用，否则将被标记为语法错误。

另外在你上面的嵌套查询中没有出错，是因为在外层中有表a的引用

多谢了！！