SQL 如何使用 ROW_NUMBER()？

How do I use ROW_NUMBER()?

提问by

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

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

I tried doing:

我试着做：

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

but it didn't seem to work...

但它似乎没有用...

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.

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

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?

有任何想法吗？

回答by Darrel Miller

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   

回答by SO User

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()：

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

2. To get the row numbers where name is 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'

1. 要获得总行数：

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

2. 要获取名称为 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(). 您可以将其用于更复杂的分组。在不使用子查询的情况下查看我对高级分组的回复

回答by Muhammad Akhtar

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 sysindexessystem table instead in this case. There is a ROWScolumn that contains the total row count for each table in your database. You can use the following select statement:

由于SELECT COUNT(*)进行全表扫描以返回行数，因此大型表可能需要很长时间。sysindexes在这种情况下，您可以改用系统表。有一ROWS列包含数据库中每个表的总行数。您可以使用以下选择语句：

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

This will drastically reduce the time your query takes.

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

回答by Jatin Phulera

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()

回答by Omid Farvid

You can use this for get first record where has clause

您可以使用它来获取第一条记录 where has 子句

SELECT TOP(1) * , ROW_NUMBER() OVER(ORDER BY UserId) AS rownum 
FROM     Users 
WHERE    UserName = 'Joe'
ORDER BY rownum ASC

回答by Jyo

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

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

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

回答by Alex Martelli

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

回答by Hari Lakkakula

select 
  Ml.Hid,
  ml.blockid,
  row_number() over (partition by ml.blockid order by Ml.Hid desc) as rownumber,
  H.HNAME 
from MIT_LeadBechmarkHamletwise ML
join [MT.HAMLE] h on ML.Hid=h.HID

回答by Gaurav Chutke

Need to create virtual table by using WITH table AS, which is mention in given Query.

需要通过 using 创建虚拟表WITH table AS，这在给定的查询中提到。

By using this virtual table, you can perform CRUD operation w.r.t row_number.

通过使用这个虚拟表，你可以执行 CRUD 操作 wrt row_number。

QUERY:

询问：

WITH table AS
-
(SELECT row_number() OVER(ORDER BY UserId) rn, * FROM Users)
-
SELECT * FROM table WHERE UserName='Joe'
-

You can use INSERT, UPDATEor DELETEin last sentence by in spite of SELECT.

您可以在最后一句中使用INSERT,UPDATE或。DELETESELECT

回答by Richard S

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