SQL 如何查找记录的行号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8738785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find row number of a record?
提问by Zo Has
Consider example table below
考虑下面的示例表
ProductDetailNo ProductDescription
224 Apples
225 Tomatoes
226 Potatoes
How do I list the row number for a selected row like below ?
如何列出所选行的行号，如下所示？
RowNo ProductDetailNo Product Description
2 225 Tomatoes
Using row_number() in my query just returns 1 always for a single record no mater what the logical row is in the database.
在我的查询中使用 row_number() 只会为单个记录始终返回 1，无论数据库中的逻辑行是什么。
Thanks, Damien.
谢谢，达米安。
回答by Shoaib Shaikh
try this
尝试这个
WITH MyTable AS
(
SELECT ProductDetailNo, ProductDescription,
ROW_NUMBER() OVER ( ORDER BY ProductDetailNo ) AS 'RowNumber'
FROM Product
)
SELECT RowNumber, ProductDetailNo
FROM MyTable
WHERE ProductDetailNo = 225
回答by Renju Vinod
Please Check This
请检查这个
WITH ArticleSearch AS
(
SELECT
ROW_NUMBER() OVER
(
ORDER BY tblProducts.ProductDetailNo
) AS RowNumber,
tblProducts.ProductDetailNo,
tblProducts.ProductDescription
FROM
tblProducts
)
SELECT
a.RowNumber AS SlNo,
a.ProductDetailNo,
a.ProductDescription
FROM
ArticleSearch a
WHERE
a.ProductDetailNo=225
回答by Gopu
WITH productCTE
AS
(SELECT ROW_NUMBER() OVER(ORDER BY ProductDetailNo, ProductDescription) AS RowNo, ProductDetailNo, ProductDescription
FROM tbl_Products
)
SELECT * FROM productCTE
WHERE RowNo = 2
回答by Sergio Tulentsev
What about this one?
这个如何？
SELECT RowNo, ProductDetailNo, ProductDescription
FROM (SELECT ROW_NUMBER() as RowNo, ProductDetailNo, ProductDescription
FROM TheTable) as t
WHERE ProductDetailNo = 225;
回答by bobbymcr
There is no inherent row number for a table row. ROW_NUMBER()gives you the number of the row only within a specific result set. So it is the expected result that you always get 1when the result set contains only 1 record. If you want a row number, your table schema should include something like an auto-incrementing IDENTITYcolumn.
表行没有固有的行号。ROW_NUMBER()仅在特定结果集中为您提供行号。因此，1当结果集仅包含 1 条记录时，您总是会得到预期的结果。如果你想要一个行号，你的表架构应该包括一个像自动递增的IDENTITY列。
回答by Vinayak Garg
The row number you receive is from number of the rows of result. i.e. if your result has just one tuple, the row no. will always be 1.
您收到的行号来自结果的行数。即如果您的结果只有一个元组，则行号。将始终为 1。
To get row number of the entire table, you should add a extra attribute, a RowNowith auto increment to your table.
要获取整个表的行号，您应该添加一个额外的属性，一个带有自动增量的RowNo到您的表。
Hope this helps, but possibly SQL has even better solution for you!
希望这会有所帮助，但 SQL 可能为您提供更好的解决方案！
