求一SQL语句，删除某一字段中的重复内容

[daodaoyu222]

例如，有表 A 中有字段 b

id b

1 5
2 6
3 7
4 5
5 7

现在要删除b中的重复内容，只保留一条数据

得到结果

id b

1 5
2 6
3 7

求这条sql语句，因数据量很大有几十万， 所以希望是 比较 优化的语句。

[ACMAIN_CHM]

delete a
from A t1,A t2
where t1.b=t2.b and t1.id>t2.id

创建索引 (b,id)

其实不直接另建一表然后插入数据 select min(id),b from a group by b

[阿_布]

delete from a t1 
where exists 
(select 1 from a where a.b=t1.b and a.id<t1.id)

[WWWWA]

delete a from a inner join (select b,max(id) as ms from a) b
on a.b=b.b and a.id=b.ma

[daodaoyu222]

为什么 delete a
from A t1,A t2
where t1.b=t2.b and t1.id>t2.id

这个不行啊，

select min(id),b from a group by b 这个能查询出来，不过是查询出来的数据，怎么插入到新表？

[ACMAIN_CHM]

mysql> select * from t_fengchujun;
+------+------+
| id   | b    |
+------+------+
|    1 |    5 |
|    2 |    6 |
|    3 |    7 |
|    4 |    5 |
|    5 |    7 |
+------+------+
5 rows in set (0.00 sec)

mysql>
mysql> delete t1
    -> from t_fengchujun t1,t_fengchujun t2
    -> where t1.b=t2.b and t1.id>t2.id;
Query OK, 2 rows affected (0.08 sec)

mysql> select * from t_fengchujun;
+------+------+
| id   | b    |
+------+------+
|    1 |    5 |
|    2 |    6 |
|    3 |    7 |
+------+------+
3 rows in set (0.00 sec)

mysql>

[vinsonshen]

delete t1 from a t1 where id not in (select min(a.id) from a where a.b=t1.b);

[阿_布]

create table table_name 
as 
select min(id),b from a group by b;

可以这样建表。

[daodaoyu222]

感谢各位。已解决

delete t1
from t_fengchujun t1,t_fengchujun t2
where t1.b=t2.b and t1.id>t2.id;

这条语句，access不能用吗？

[ACMAIN_CHM]

[Quote]这条语句，access不能用吗？ [/Quote]

不能，各个数据库之间都不太一样。

access 中的话，你可以直接用#6楼 的这种

[daodaoyu222]

ACMAIN_CHM

我现在 mysql中用

delete t1
from t_fengchujun t1,t_fengchujun t2
where t1.b=t2.b and t1.id>t2.id;

这条语句

可是结果总是 Got error 134 from storage engine

我修复了，又是这样？