如何使用 ROW_NUMBER()？

发布于 2024-07-24 07:28:04 字数 506 浏览 11 评论 0原文

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

获取 max(ROW_NUMBER()) --> 所有行的计数

或者我想这也将是我尝试执行的

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

：但它似乎不起作用...

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

我认为它会类似于我尝试的#1，

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

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

有什么想法吗？

原文

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

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

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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

追风人 2024-07-31 07:28:04

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

SELECT COUNT(*) FROM myTable

得到计数。

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

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

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

那么当您想要返回 5 行时，您可以获取当前行号并使用以下查询来确定 currentrow -5 的行

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

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,

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

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

回复收藏 0 原文

乖不如嘢 2024-07-31 07:28:04

虽然我同意其他人的观点，您可以使用 count() 来获取总行数，但以下是如何使用 row_count()：

要获取总行数：

温度为 ( 
      选择 row_number() over (order by id) as rownum 
      来自表名  
  ） 
  select max(rownum) from temp

获取名称为 Matt 的行号：

温度为 ( 
      选择名称，row_number() over (order by id) as rownum 
      来自表名 
  ） 
  select rownum from temp where name like 'Matt'

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

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

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

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:

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.

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

回复收藏 0 原文

浅语花开 2024-07-31 07:28:04

如果您需要返回表的总行数，可以使用 SELECT COUNT(*) 语句的替代方法。

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

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

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

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

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 rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

This will drastically reduce the time your query takes.

回复收藏 0 原文

踏月而来 2024-07-31 07:28:04

ROW_NUMBER() 为每行返回一个从 1 开始的唯一编号。您只需编写以下代码即可轻松使用它：

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

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

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

回复收藏 0 原文

臻嫒无言 2024-07-31 07:28:04

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

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

You can use this for get first record where has clause

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

回复收藏 0 原文

葮薆情 2024-07-31 07:28:04

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

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

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

回复收藏 0 原文

幽蝶幻影 2024-07-31 07:28:04

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

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

回复收藏 0 原文

昨迟人 2024-07-31 07:28:04

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

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

回复收藏 0 原文

七颜 2024-07-31 07:28:04

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

例子：

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

-
通过上面的查询，我将从 TABLENAME 中获得第 1 页的结果。

You can use Row_Number for limit query result.

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.

回复收藏 0 原文

度的依靠╰つ 2024-07-31 07:28:04

如果您绝对想为此使用 ROW_NUMBER （而不是 count(*)），您可以随时使用：

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

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

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

回复收藏 0 原文

命比纸薄 2024-07-31 07:28:04

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

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

查询：

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

尽管使用了SELECT，但您仍可以在最后一句中使用INSERT、UPDATE 或DELETE。

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

By using this virtual table, you can perform CRUD operation w.r.t 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, UPDATE or DELETE in last sentence by in spite of SELECT.

回复收藏 0 原文

怪我鬧 2024-07-31 07:28:04

SQL Row_Number() 函数是对相关记录集中的数据行进行排序并分配序号。因此它用于对行进行编号，例如识别订单金额最高的前 10 行或识别金额最高的每个客户的订单等。

如果您想对数据集进行排序并通过分隔每一行进行编号我们使用 Row_Number() 和 Partition By 子句将它们分成类别。例如，对每个客户内部的订单进行排序，其中数据集包含所有订单等。

SELECT
    SalesOrderNumber,
    CustomerId,
    SubTotal,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY SubTotal DESC) rn
FROM Sales.SalesOrderHeader

但据我了解，您想要计算按列分组的行数。为了可视化需求，如果您希望除了订单信息之外以单独的列形式查看相关客户的所有订单计数，您可以使用 COUNT() 带有 Partition By 子句的聚合函数

例如，

SELECT
    SalesOrderNumber,
    CustomerId,
    COUNT(*) OVER (PARTITION BY CustomerId) CustomerOrderCount
FROM Sales.SalesOrderHeader

SQL Row_Number() function is to sort and assign an order number to data rows in related record set. So it is used to number rows, for example to identify the top 10 rows which have the highest order amount or identify the order of each customer which is the highest amount, etc.

If you want to sort the dataset and number each row by seperating them into categories we use Row_Number() with Partition By clause. For example, sorting orders of each customer within itself where the dataset contains all orders, etc.

SELECT
    SalesOrderNumber,
    CustomerId,
    SubTotal,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY SubTotal DESC) rn
FROM Sales.SalesOrderHeader

But as I understand you want to calculate the number of rows of grouped by a column. To visualize the requirement, if you want to see the count of all orders of the related customer as a seperate column besides order info, you can use COUNT() aggregation function with Partition By clause

For example,

SELECT
    SalesOrderNumber,
    CustomerId,
    COUNT(*) OVER (PARTITION BY CustomerId) CustomerOrderCount
FROM Sales.SalesOrderHeader

回复收藏 0 原文

药祭#氼 2024-07-31 07:28:04

此查询：

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

将返回 UserName 为 'Joe' 的所有行，除非您没有 UserName='Joe'

它们将按顺序列出UserID 和 row_number 字段将从 1 开始并递增，但许多行包含 UserName='Joe'

如果它不适合您，那么您的 WHERE 命令有问题或者表中没有 UserID。检查 UserID 和 UserName 字段的拼写。

This query:

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

will return all rows where the UserName is 'Joe' UNLESS you have no UserName='Joe'

They will be listed in order of UserID and the row_number field will start with 1 and increment however many rows contain UserName='Joe'

If it does not work for you then your WHERE command has an issue OR there is no UserID in the table. Check spelling for both fields UserID and UserName.

回复收藏 0 原文

~没有更多了~