例如,有表 A 中有字段 bid b1 5
2 6
3 7
4 5
5 7现在要删除b中的重复内容,只保留一条数据得到结果
id b1 5
2 6
3 7求这条sql语句,因数据量很大有几十万, 所以希望是 比较 优化的语句。
2 6
3 7
4 5
5 7现在要删除b中的重复内容,只保留一条数据得到结果
id b1 5
2 6
3 7求这条sql语句,因数据量很大有几十万, 所以希望是 比较 优化的语句。
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)
on a.b=b.b and a.id=b.ma
from A t1,A t2
where t1.b=t2.b and t1.id>t2.id这个不行啊,select min(id),b from a group by b 这个能查询出来,不过是查询出来的数据,怎么插入到新表?
+------+------+
| 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>
create table table_name
as
select min(id),b from a group by b;可以这样建表。
from t_fengchujun t1,t_fengchujun t2
where t1.b=t2.b and t1.id>t2.id;这条语句,access不能用吗?
不能,各个数据库之间都不太一样。access 中的话,你可以直接用#6楼 的这种
from t_fengchujun t1,t_fengchujun t2
where t1.b=t2.b and t1.id>t2.id; 这条语句可是结果总是 Got error 134 from storage engine 我修复了,又是这样?