我可以获取一条记录在 SQL 结果表中的位置吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/907438/
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
Can I get the position of a record in a SQL result table?
提问by Andomar
If I do something like
如果我做类似的事情
SELECT * FROM mytable ORDER BY mycolumn ASC;
I get a result table in a specific order.
我得到一个特定顺序的结果表。
Is there a way in SQL to efficiently find out, given a PK, what position in that result table would contain the record with my PK?
有没有办法在 SQL 中有效地找出给定 PK，结果表中的哪个位置将包含我的 PK 记录？
回答by Guffa
You can count the number of records where the value that you are sorting on has a lower value than the record that you know the key value of:
您可以计算您正在排序的值的值低于您知道其键值的记录的记录数：
select count(*)
from mytable
where mycolumn < (select mycolumn from mytable where key = 42)
回答by Andomar
On databases that support it, you could use ROW_NUMBER() for this purpose:
在支持它的数据库上，您可以为此使用 ROW_NUMBER()：
SELECT RowNr
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr,
mycolumn
FROM mytable
) sub
WHERE sub.mycolumn = 42
The example assumes you're looking for primary key 42 :)
该示例假设您正在寻找主键 42 :)
The subquery is necessary because something like:
子查询是必要的，因为类似于：
SELECT
ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr
FROM mytable
WHERE sub.mycolumn = 42
Will always return 1; ROW_NUMBER() works after the WHERE, so to speak.
将始终返回 1；ROW_NUMBER() 在 WHERE 之后工作，可以这么说。
回答by duffymo
SQL doesn't work that way. It's set-based, which means that "position in that result table" is meaningless to the database.
SQL 不是这样工作的。它是基于集合的，这意味着“该结果表中的位置”对数据库毫无意义。
You can keep track of position when you map the ResultSet into a collection of objects or when you iterate over it.
当您将 ResultSet 映射到对象集合或对其进行迭代时，您可以跟踪位置。
回答by Lasse V. Karlsen
Unfortunately you cannot get "the position of a row in a table".
不幸的是，您无法获得“表格中一行的位置”。
The best you can get, using ORDER BY and a variant of the ROW_NUMBER construct (depends on the database engine in use), is the position of a row in the resultset of the query executed.
使用 ORDER BY 和 ROW_NUMBER 构造的变体（取决于所使用的数据库引擎），您可以获得的最佳结果是执行查询的结果集中行的位置。
This position does not map back to any position in the table, though, unless the ORDER BY is on a set of clustered index columns, but even then that position might be invalidated the next second.
但是，此位置不会映射回表中的任何位置，除非 ORDER BY 位于一组聚集索引列上，但即使如此，该位置也可能在下一秒失效。
What I would like to know is what you intended to use this "position" for.
我想知道的是你打算用这个“职位”做什么。
回答by micaball
This answer applies to MySQL
这个答案适用于 MySQL
==> lower than 8.0
==> 低于 8.0
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num,
myColumn.first,
myColumn.second
FROM
myTable
ORDER BY myColumn.first, myColumn.second
source: http://www.mysqltutorial.org/mysql-row_number/
来源：http: //www.mysqltutorial.org/mysql-row_number/
==> greater than 8.0
==> 大于 8.0
Please see MySQL ROW_NUMBER() function manualas I did not test. But it seems this function is prefered.
请参阅MySQL ROW_NUMBER() 函数手册，因为我没有测试。但似乎这个功能是首选。
回答by Anton Gogolev
There's no way you can tell that without selecting an entire subset of records. If your PK is of integer type, you can
如果不选择整个记录子集，您就无法判断。如果你的 PK 是整数类型，你可以
select count(*) from mytable
where id <= 10 -- Record with ID 10
order by mycolumn asc
