如何使用 ROW_NUMBER()? [英] How do I use ROW_NUMBER()?

问题描述

我想使用 ROW_NUMBER() 来获取...

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

1. 获得 max(ROW_NUMBER()) --> 或者我想这也是所有行的计数

1. To get the max(ROW_NUMBER()) --> Or i guess this would also be the count of all rows

我尝试过:

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

但它似乎不起作用...

but it didn't seem to work...

1. 使用给定的信息获取 ROW_NUMBER()，即.如果我有名字并且我想知道名字来自哪一行.

1. 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.

我认为这与我尝试的 #1 类似

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'

但这也没有用...

有什么想法吗?

推荐答案

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

For the first question, why not just use?

SELECT COUNT(*) FROM myTable 

获取计数.

对于第二个问题，行的主键是用来标识特定行的.不要尝试使用行号.

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.

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

If you returned Row_Number() in your main query,

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

然后当你想要返回 5 行时，你可以获取当前行号并使用以下查询来确定具有 currentrow -5 的行

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

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

这篇关于如何使用 ROW_NUMBER()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！