从表中删除Top-N‘行，并进行排序(按’Column‘排序)

Question

我对删除按某列排序的前N行有些困惑。

我在这里创建了一个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关键字可以很好地完成这项工作

Answer 1

您可以使用CTE执行faster ordered delete，而不需要单独的子查询来检索前3个ids。

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

Answer 2

将top 3子句添加到子选择中：

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