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

问题描述

我有一个sql查询：

  select id，name from table order by name 
  
 
 
 
结果如下：
  52 arnold 
 33 berta 
 34 chris 
 47 doris 
 52 emil 
  
对于给定的id = 47如何确定在结果集中的位置？ 
结果应为4，因为：
  52 arnold 
 33 berta 
 34 chris 
  
在结果集的第4个位置（47，doris）和id = 41。 / p> 
 
 
如何在SQL中执行此操作？ 
如何在HQL？ 
在一个分页的例子中，我必须执行2个语句，或者有一个解决方案，我可以检索到包含id = 47的行的窗口。
 
 
 
 postgreSQL和java 
解决方案
如果使用Microsoft SQL Server 2005或更高版本，请使用ROW_NUMBER。
 
 
 
但是，您的代码没有指定您使用的是MSSQL，所以这里有一个解决方案，实现。本质上，使用关联子查询根据外部查询的ORDER子句的值确定同一集合中小于当前行的行数。类似这样：
  SELECT T1.id，
 T1。[name]，
 （*）
 FROM table T2 
 WHERE T2。[name]< T1。[name]）+ 1 AS rowpos 
 FROM table T1 
 ORDER BY T1。[name] 
  
 
i have a sql query:
select id, name from table order by name
result looks like this:
52 arnold 
33 berta 
34 chris 
47 doris
52 emil
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
are before (47, doris) and id=41 is on the 4th position in the result set.

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 and java
解决方案
The previous posts are correct. Use ROW_NUMBER if using Microsoft SQL Server 2005 or greater.

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屋！