删除全部数据，但保留最后添加的10条，SQL语句怎么写？在线等！
RT，
删除一个表的全部数据，仅保留最后添加的10条。
求个SQL。。
这样写行不通：
delete from `onlist` where `id` not in (select `id` from `onlist` order by `id` desc limit 10)
——解决方案——————–
delete from onlist where id not in (select TOP 10 id from onlist order by id desc)
——解决方案——————–
mysql暂时不支持子查询中引用自己的字段。
不过有折中的方案：
mysql> select * from table_a;
+—-+———–+——+
| id | parent_id | name |
+—-+———–+——+
| 0 | -1 | – |
| 1 | 0 | a |
| 2 | 1 | b |
| 3 | 1 | c |
| 4 | 1 | d |
| 5 | 2 | e |
| 6 | 2 | f |
| 7 | 3 | g |
| 8 | 4 | h |
| 9 | 6 | i |
| 10 | 8 | j |
| 11 | 9 | k |
| 12 | 11 | l |
+—-+———–+——+
13 rows in set (0.00 sec)
mysql> create temporary table tmp select * from table_a order by id desc limit 1
0;
Query OK, 10 rows affected (0.16 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> truncate table table_a;
Query OK, 13 rows affected (0.08 sec)
mysql> insert into table_a select * from tmp;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from table_a;
+—-+———–+——+
| id | parent_id | name |
+—-+———–+——+
| 3 | 1 | c |
| 4 | 1 | d |
| 5 | 2 | e |
| 6 | 2 | f |
| 7 | 3 | g |
| 8 | 4 | h |
| 9 | 6 | i |
| 10 | 8 | j |
| 11 | 9 | k |
| 12 | 11 | l |
+—-+———–+——+
10 rows in set (0.00 sec)
mysql>
转载请注明原文链接：删除全部数据，但保留最后添加的10条，SQL语句如何写？
