从 SQL Server 中的表中删除“第一条”记录，不带 WHERE 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/733668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete the 'first' record from a table in SQL Server, without a WHERE condition
提问by Dhana
Is it possible to delete the 'first' record from a table in SQL Server, without using any WHEREcondition and without using a cursor?
是否可以在SQL Server不使用任何WHERE条件且不使用游标的情况下从表中删除“第一条”记录？
回答by Quassnoi
WITH q AS
(
SELECT TOP 1 *
FROM mytable
/* You may want to add ORDER BY here */
)
DELETE
FROM q
Note that
注意
DELETE TOP (1)
FROM mytable
will also work, but, as stated in the documentation:
也可以工作，但是，如文档中所述：
The rows referenced in the
TOPexpression used withINSERT,UPDATE, orDELETEare not arranged in any order.
在所引用的行
TOP表达与所用INSERT，UPDATE或DELETE不按任何顺序排列。
Therefore, it's better to use WITHand an ORDER BYclause, which will let you specify more exactly which row you consider to be the first.
因此，最好使用WITHandORDER BY子句，它可以让您更准确地指定您认为哪一行是第一行。
回答by soulmerge
回答by Alnitak
No, AFAIK, it's not possible to do it portably.
不，AFAIK，它不可能便携。
There's no defined "first" record anyway - on different SQL engines it's perfectly possible that "SELECT * FROM table" might return the results in a different order each time.
无论如何都没有定义的“第一”记录 - 在不同的 SQL 引擎上，“ SELECT * FROM table”每次都可能以不同的顺序返回结果。
回答by Meff
Define "First"? If the table has a PK then it will be ordered by that, and you can delete by that:
定义“第一”？如果表有一个 PK 那么它会被排序，你可以删除：
DECLARE @TABLE TABLE
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Data NVARCHAR(50) NOT NULL
)
INSERT INTO @TABLE(Data)
SELECT 'Hello' UNION
SELECT 'World'
SET ROWCOUNT 1
DELETE FROM @TABLE
SET ROWCOUNT 0
SELECT * FROM @TABLE
If the table has no PK, then ordering won't be guaranteed...
如果该表没有PK，则无法保证订购...
回答by Christian Specht
Does this really make sense?
There is no "first" record in a relational database, you can only delete one random record.
这真的有意义吗？
关系数据库中没有“第一条”记录，只能随机删除一条记录。
回答by XpiritO
What do you mean by ?'first' record from a table? ? There's no such concept as "first record" in a relational db, i think.
表中的“第一条”记录是什么意思？? 我认为，在关系数据库中没有“第一条记录”这样的概念。
Using MS SQL Server 2005, if you intend to delete the "top record" (the first one that is presented when you do a simple "*select * from tablename*"), you may use "delete top(1) from tablename"... but be aware that this does not assure which row is deleted from the recordset, as it just removes the first row that would be presented if you run the command "select top(1) from tablename".
使用 MS SQL Server 2005，如果您打算删除“顶级记录”（当您执行简单的“*select * from tablename*”时出现的第一个记录），您可以使用“ delete top(1) from tablename” ...但请注意，这并不能确保从记录集中删除哪一行，因为它只会删除在您运行命令“ select top(1) from tablename”时将显示的第一行。
回答by Gerard ONeill
Similar to the selected answer, a table source can be used, in this case a derived query:
与所选答案类似，可以使用表源，在本例中为派生查询：
delete from dd
from (
select top 1 *
from my_table
) dd
Feel free to add orderbys and conditions.
随意添加订单和条件。
For the next example, I'll assume that the restriction on 'where' is due to not wanting to select a row based on its values. So assuming that we want to delete a row based on position (in this case the first position):
对于下一个示例，我将假设对“where”的限制是由于不想根据其值选择一行。因此，假设我们要根据位置（在本例中为第一个位置）删除一行：
delete from dd
from (
select
*,
row = row_number() over (order by (select 1))
from my_table
) dd
where row = 1
Note that the (select 1) makes it the sort order that the tables or indexes are in. You can replace that with a newid to get fairly random rows.
请注意， (select 1) 使其成为表或索引所在的排序顺序。您可以将其替换为 newid 以获得相当随机的行。
You can also add a partition by to delete the top row of each color, for example.
例如，您还可以通过删除每种颜色的顶行来添加分区。
回答by GPTechnologies
SQL-92:
SQL-92：
DELETE Field FROM Table WHERE Field IN (SELECT TOP 1 Field FROM Table ORDER BY Field DESC)
