使用某种排序从表中删除前 N 行(按“列"排序) [英] Delete Top-N' Rows from a Table with some sorting(order by 'Column')

问题描述

我对按某些列删除前 N 行顺序有些困惑.

I am having some confusion regarding Deleting the top N Rows order by some column.

我在这里创建了一个示例 示例在小提琴

I created have an example here Example at fiddle

这些查询有什么问题?

 Delete Top(3) from Table1 order by id desc

 Delete Top(3) from Table1 
 where id IN (select id from Table1 order by id desc)

因为在 mysql 中 limit 关键字可以很好地完成这项工作

Since in mysql the limit keyword does the job very well

推荐答案

您可以使用 CTE 来执行 更快的有序删除，无需单独的子查询来检索前 3 个 ID.

You can use a CTE to do a faster ordered delete without the need for a separate sub query to retrieve the top 3 ids.

WITH T
     AS (SELECT TOP 3 *
         FROM   Table1
         ORDER  BY id DESC)
DELETE FROM T 

这篇关于使用某种排序从表中删除前 N 行(按“列"排序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！