如何使用ROW_NUMBER()?

I want to use the ROW_NUMBER() to get...

我想用ROW_NUMBER()来得到…

To get the max(ROW_NUMBER()) --> Or i guess this would also be the count of all rows
要获得max(ROW_NUMBER())——>或者我猜这也是所有行的计数

I tried doing:

我试着做的事情:

SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users

but it didn't seem to work...

但它似乎不管用……

To get ROW_NUMBER() using a given piece of information, ie. if I have a name and I want to know what row the name came from.
使用给定的信息获取ROW_NUMBER()。如果我有一个名字，我想知道这个名字来自哪一行。

I assume it would be something similar to what I tried for #1

我想它应该和我试过的第一点相似

SELECT ROW_NUMBER() OVER(ORDER BY UserId) From Users WHERE UserName='Joe'

but this didn't work either...

但这也不管用……

Any Ideas?

什么好主意吗?

9 个解决方案

#1

109

For the first question, why not just use?

对于第一个问题，为什么不直接使用呢?

SELECT COUNT(*) FROM myTable

to get the count.

计数。

And for the second question, the primary key of the row is what should be used to identify a particular row. Don't try and use the row number for that.

对于第二个问题，这一行的主键是用来识别某一行的。不要尝试使用行号。

If you returned Row_Number() in your main query,

如果在主查询中返回Row_Number()，

SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber, Field1, Field2, Field3
FROM User

Then when you want to go 5 rows back then you can take the current row number and use the following query to determine the row with currentrow -5

然后，当您想要返回5行时，可以使用当前行号，并使用下面的查询来确定与currentrow -5的行。

SELECT us.Id
FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS Row, Id
     FROM User ) us 
WHERE Row = CurrentRow - 5

#2

Though I agree with others that you could use count() to get the total number of rows, here is how you can use the row_count():

虽然我同意您可以使用count()获取总行数，但以下是如何使用row_count()的方法:

To get the total no of rows:

得到行总数:

with temp as (
    select row_number() over (order by id) as rownum
    from table_name 
)
select max(rownum) from temp

To get the row numbers where name is Matt:

要获取名称为Matt的行号:

with temp as (
    select name, row_number() over (order by id) as rownum
    from table_name
)
select rownum from temp where name like 'Matt'

You can further use min(rownum) or max(rownum) to get the first or last row for Matt respectively.

您可以进一步使用min(rownum)或max(rownum)分别获得Matt的第一行或最后一行。

These were very simple implementations of row_number(). You can use it for more complex grouping. Check out my response on Advanced grouping without using a sub query

这些是row_number()的非常简单的实现。您可以使用它进行更复杂的分组。检查我对高级分组的响应，而不使用子查询

#3

If you need to return the table's total row count, you can use an alternative way to the SELECT COUNT(*) statement.

如果需要返回表的总行数，可以使用SELECT count(*)语句的另一种方法。

Because SELECT COUNT(*) makes a full table scan to return the row count, it can take very long time for a large table. You can use the sysindexes system table instead in this case. There is a ROWS column that contains the total row count for each table in your database. You can use the following select statement:

因为SELECT COUNT(*)会对整个表进行扫描，以返回行计数，所以对于大型表来说，这需要很长时间。在这种情况下，您可以使用sysindexes系统表。有一个行列，其中包含数据库中每个表的总行数。您可以使用以下选择语句:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

This will drastically reduce the time your query takes.

这将大大减少查询所需的时间。

#4

ROW_NUMBER() returns a unique number for each row starting with 1. You can easily use this by simply writing:

ROW_NUMBER()为以1开头的每一行返回唯一的数字。你可以简单地写上:

ROW_NUMBER() OVER (ORDER BY 'Column_Name' DESC) as ROW_NUMBER

You can find the difference between Row_number(), Rank() and Dense_Rank() here.

您可以在这里找到Row_number()、Rank()和Dense_Rank()之间的区别。

#5

SELECT num, UserName FROM 
 (SELECT UserName, ROW_NUMBER() OVER(ORDER BY UserId) AS num
  From Users) AS numbered
WHERE UserName='Joe'

#6

Taking your heading literal, here is a blog entry explaining the so called window functions (where row_number is one of them):

以你的标题文字，这里有一个博客条目解释所谓的窗口函数(其中row_number是其中之一):

http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions/

http://www.depesz.com/index.php/2009/01/21/waiting - - 84窗口functions/

#7

If you absolutely want to use ROW_NUMBER for this (instead of count(*)) you can always use:

如果您绝对想要使用ROW_NUMBER(而不是count(*)))，您可以使用:

SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY Id)   
FROM USERS  
ORDER BY ROW_NUMBER() OVER (ORDER BY Id) DESC

#8

You can use Row_Number for limit query result.

您可以使用Row_Number来限制查询结果。

Example:

例子:

SELECT * FROM (
    select row_number() OVER (order by createtime desc) AS ROWINDEX,* 
    from TABLENAME ) TB
WHERE TB.ROWINDEX between 0 and 10

-- With above query, I will get PAGE 1 of results from TABLENAME.

——使用上面的查询，我将从TABLENAME中获得第1页的结果。

#9

May not be related to the question here. But I found it could be useful when using row number -

可能与这里的问题无关。但是我发现它在使用行号-时是有用的

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS Any_ID FROM #Any_Table

从#Any_Table中选择*、ROW_NUMBER() OVER (ORDER BY (SELECT 100))作为Any_ID

#1

109