sql - 如何让 DELETE FROM [QUERY] 起作用？

Question

您好，我是 mssql 的新手，我习惯使用 Oracle。我正在尝试从子查询中删除特定行，但 mssql 并不真正喜欢子查询。

这里是查询:

DELETE FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row FROM randomtable) a
WHERE a.row = 1

有没有办法让它工作？

在 Oracle 中，我可以在查询中获取所有内容，因为我可以使用 rownum = 1。

Answer 1

你快到了

DELETE a
FROM   (SELECT *,
               ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row
        FROM   randomtable) a
WHERE  a.row = 1 

虽然我更喜欢 CTE 语法

WITH a
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row
         FROM   randomtable)
DELETE FROM a
WHERE  a.row = 1