sql - 通过某种排序从表中删除前 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 个 ID。

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