我可以获取一条记录在 SQL 结果表中的位置吗?

问题描述：

如果我做类似的事情

SELECT * FROM mytable ORDER BY mycolumn ASC;

我按特定顺序得到结果表.

I get a result table in a specific order.

在 SQL 中是否有一种方法可以有效地找出给定 PK 的结果表中的哪个位置将包含我的 PK 记录?

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?

答

您可以计算您正在排序的值比您知道其键值的记录的值低的记录数:

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)