假如你的表有ID唯一值列的话,则:select * from tb_name a where not exists (select 1 from tb_name b where b.phoneNo=a.phoneNo and b.ID>a.ID) 如果没有类似ID这里唯一值列的话,那考虑中间转换成有这样唯一值的列才处理。如果只是要phoneNo这一列的话,那直接:select phoneNo from a group by phoneNo;
mysql> select * from tb; +------+------+ | rid | con | +------+------+ | 1 | b | | 2 | b | | 3 | b | | 1 | a | +------+------+ 4 rows in set (0.00 sec)mysql> delete from tb where rid in (select rid from (select rid,count(rid) c fr om tb group by rid having c>1) t); Query OK, 2 rows affected (0.03 sec)mysql> select * from tb; +------+------+ | rid | con | +------+------+ | 2 | b | | 3 | b | +------+------+ 2 rows in set (0.00 sec)
mysql> delete from gztemp where phoneNo in(select phoneNo from(select phoneNo,count(phoneNo)c from gztemp group by phoneNo having c>1)t); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> 我怎么出现了 这个问题 是不是我的数据量太大?
如果没有类似ID这里唯一值列的话,那考虑中间转换成有这样唯一值的列才处理。如果只是要phoneNo这一列的话,那直接:select phoneNo from a group by phoneNo;
+------+------+
| rid | con |
+------+------+
| 1 | b |
| 2 | b |
| 3 | b |
| 1 | a |
+------+------+
4 rows in set (0.00 sec)mysql> delete from tb where rid in (select rid from (select rid,count(rid) c fr
om tb group by rid having c>1) t);
Query OK, 2 rows affected (0.03 sec)mysql> select * from tb;
+------+------+
| rid | con |
+------+------+
| 2 | b |
| 3 | b |
+------+------+
2 rows in set (0.00 sec)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
我怎么出现了 这个问题 是不是我的数据量太大?
mysql初学者!