sql - 我如何使用ROW_NUMBER（）？(How do I use ROW_NUMBER()?)

Question

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

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

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

(但它似乎没有工作......)

2. To get ROW_NUMBER() using a given piece of information, ie.

   (使用给定的信息来获得ROW_NUMBER() ，即。)

   if I have a name and I want to know what row the name came from.

   (如果我有一个名字，我想知道名字来自哪一行。)

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

(我认为这将类似于我为＃1尝试的东西)

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

but this didn't work either...

(但这也不起作用......)

Any Ideas?

(有任何想法吗？)

  ask by translate from so

Answer 1

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