- mysql> select * from t2;
- +----+----------+---------+
- | id | SerialNO | InnerNo |
- +----+----------+---------+
- | 1 | 19 | 10 |
- | 2 | 16 | 10 |
- | 3 | 99 | 10 |
- | 4 | 17 | 10 |
- | 5 | 53 | 11 |
- | 6 | 67 | 11 |
- | 7 | 89 | 12 |
- +----+----------+---------+
- 7 rows in set (0.02 sec)
需求:
这样的表,要删除innerNO对应的SerialNO值不是最大的所有数据。
例如innerNO=10,这个对应SerialNO值是19,16,99,17,只留这4条最大的SerialNO值,其他的都给删了。
- mysql> select a.*,b.* from t2 a left join t2 b on a.InnerNO=b.InnerNO where a.SerialNO < b.SerialNO group by a.SerialNO;
- +----+----------+---------+------+----------+---------+
- | id | SerialNO | InnerNo | id | SerialNO | InnerNo |
- +----+----------+---------+------+----------+---------+
- | 2 | 16 | 10 | 1 | 19 | 10 |
- | 4 | 17 | 10 | 1 | 19 | 10 |
- | 1 | 19 | 10 | 3 | 99 | 10 |
- | 5 | 53 | 11 | 6 | 67 | 11 |
- +----+----------+---------+------+----------+---------+
- 4 rows in set (0.05 sec)
- mysql> delete a from t2 a join t2 b on a.InnerNO=b.InnerNO where a.SerialNO < b.SerialNO ;
- Query OK, 4 rows affected (0.20 sec)
- mysql> select * from t2;
- +----+----------+---------+
- | id | SerialNO | InnerNo |
- +----+----------+---------+
- | 3 | 99 | 10 |
- | 6 | 67 | 11 |
- | 7 | 89 | 12 |
- +----+----------+---------+
- 3 rows in set (0.03 sec)
本文转自hcymysql51CTO博客,原文链接:http://blog.51cto.com/hcymysql/1040888 ,如需转载请自行联系原作者