从具有单个列的表中删除除前N个之外的所有行 [英] Delete all rows except first N from a table having single column
本文介绍了从具有单个列的表中删除除前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个之外的所有行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文