从具有单个列的表中删除除前N个之外的所有行

问题描述：

我需要一个查询. Delete表中除前N行之外的所有行.该表只有一列.喜欢，

I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,

|friends_name|
==============
| Arunji     |
| Roshit     |
| Misbahu    |
| etc...     |

此列也可能包含重复的名称.

This column may contain repeated names as well.

包含重复的名称

Contains repeated names

只有一列.

答

如果可以按friends_name排序记录，并且没有重复项，则可以使用以下方法:

If you can order your records by friends_name, and if there are no duplicates, you could use this:

DELETE FROM names
WHERE
  friends_name NOT IN (
    SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
  )

请在此处看到小提琴.

或者您可以使用此:

DELETE FROM names ORDER BY friends_name DESC
LIMIT total_records-10

total_records是(SELECT COUNT(*) FROM names)，但是您必须通过代码执行此操作，因此无法在查询的LIMIT子句中添加计数.

where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.

从具有单个列的表中删除除前N个之外的所有行

相关推荐