如何确定sql结果集中行的位置? [英] How to determine position of row in sql result-set?

问题描述

我有一个 sql 查询:

i have a sql query:

select id, name from table order by name

结果如下:

52 arnold 
33 berta 
34 chris 
47 doris
52 emil

对于给定的 id=47，我如何确定结果集中的位置?结果应该是 4，因为:

for a given id=47 how can i determine the position in the result set? the result should be 4 because:

52 arnold
33 berta
34 chris

在 (47, doris) 之前，id=41 在结果集中的第 4 位.

are before (47, doris) and id=41 is on the 4th position in the result set.

如何在 SQL 中执行此操作?在 HQL 中如何?在分页示例中，我是否必须执行 2 条语句，或者是否有解决方案可以准确检索包含 id=47 的行的窗口?

How to do this in SQL? How in HQL? In a pagination example, do i have to execute 2 statements or is there a solution where i can retrieve exactly that window which contains the row with id=47?

postgreSQL 和 java

postgreSQL and java

推荐答案

之前的帖子都是正确的.如果使用 Microsoft SQL Server 2005 或更高版本，请使用 ROW_NUMBER.

The previous posts are correct. Use ROW_NUMBER if using Microsoft SQL Server 2005 or greater.

但是，您的标签并未指明您使用的是 MSSQL，因此这里提供了一个适用于大多数 RDBMS 实现的解决方案.本质上，根据外部查询的 ORDER 子句的值，使用相关子查询来确定同一集中小于当前行的行数.像这样:

However, your tags do not specify that you're using MSSQL, so here's a solution that should work across most RDBMS implementations. Essentially, use a correlated subquery to determine the count of rows in the same set that are less than the current row, based on the values of the ORDER clause of the outer query. Something like this:

SELECT      T1.id,
            T1.[name],
            (SELECT COUNT(*) 
             FROM table T2 
             WHERE T2.[name] < T1.[name]) + 1 AS rowpos
FROM        table T1
ORDER BY    T1.[name]

这篇关于如何确定sql结果集中行的位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！