1.原表
mysql> select * from t_value;
+----+------------+
| id | value |
+----+------------+
| 1 | 1212 |
| 1 | 1234342 |
| 1 | 12 |
| 1 | 1243242341 |
| 2 | 43242341 |
| 2 | 43241 |
| 2 | 4333331 |
| 2 | 4 |
| 2 | 432456666 |
+----+------------+
9 rows in set
2. 期望以上id為1，2的分別只保留兩條
3.新建一個(gè)臨時(shí)表，字段同上，并新增一個(gè)num字段為自增。并插入
insert into t_tmp(id,value) select id,value from t_value order by id;
4.于是t_tmp如下：
mysql> select * from t_tmp;
+----+------------+-----+
| id | value | num |
+----+------------+-----+
| 1 | 1212 | 79 |
| 1 | 1234342 | 80 |
| 1 | 12 | 81 |
| 1 | 1243242341 | 82 |
| 2 | 43242341 | 83 |
| 2 | 43241 | 84 |
| 2 | 4333331 | 85 |
| 2 | 4 | 86 |
| 2 | 432456666 | 87 |
+----+------------+-----+
5.再通過(guò)以下SQL查出
mysql> select t_tmp.id,t_tmp.value,t_tmp.num from t_tmp
left join(select num,id,value from t_tmp group by id) as t
on t.id=t_tmp.id
where t_tmp.num<t.num+2;
+----+----------+-----+
| id | value | num |
+----+----------+-----+
| 1 | 1212 | 79 |
| 1 | 1234342 | 80 |
| 2 | 43242341 | 83 |
| 2 | 43241 | 84 |
+----+----------+-----+