SQL 获取查询的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9151706/
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
Getting row number for query
提问by Echilon
I have a query which will return one row. Is there any way I can find the row index of the row I'm querying when the table is sorted?
我有一个将返回一行的查询。有什么办法可以在对表进行排序时找到我正在查询的行的行索引？
I've tried rowidbut got #582 when I was expecting row #7.
我已经尝试过，rowid但是当我期待第 7 行时得到了 #582。
Eg:
例如：
CategoryID Name
I9GDS720K4 CatA
LPQTOR25XR CatB
EOQ215FT5_ CatC
K2OCS31WTM CatD
JV5FIYY4XC CatE
--> C_L7761O2U CatF <-- I want this row (#5)
OU3XC6T19K CatG
L9YKCYAYMG CatH
XKWMQ7HREG CatI
I've tried rowid with unexpected results:
我试过 rowid 有意想不到的结果：
SELECT rowid FROM Categories WHERE CategoryID = 'C_L7761O2U ORDER BY Name
EDIT:I've also tried J Cooper's suggestion (below), but the row numbers just aren't right.
编辑：我也尝试过 J Cooper 的建议（如下），但行号不正确。
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = string.Format(@"SELECT (SELECT COUNT(*) FROM Recipes AS t2 WHERE t2.RecipeID <= t1.RecipeID) AS row_Num
FROM Recipes AS t1
WHERE RecipeID = 'FB3XSAXRWD'
ORDER BY Name";
cmd.Parameters.AddWithValue("@recipeId", id);
idx = Convert.ToInt32(cmd.ExecuteScalar());
回答by J Cooper
Here is a way to get the row number in Sqlite:
这是一种在Sqlite 中获取行号的方法：
SELECT CategoryID,
Name,
(SELECT COUNT(*)
FROM mytable AS t2
WHERE t2.Name <= t1.Name) AS row_Num
FROM mytable AS t1
ORDER BY Name, CategoryID;
回答by Micha
Here's a funny trick you can use in Spatialite to get the order of values. If you use the count() function with a WHERE clause limiting to only values >= the current value, then the count will actually give the order. So if I have a point layer called "mypoints" with columns "value" and "val_order" then:
这是一个有趣的技巧，您可以在 Spatialite 中使用它来获取值的顺序。如果将 count() 函数与 WHERE 子句一起使用，限制为仅值 >= 当前值，则计数实际上将给出顺序。因此，如果我有一个名为“mypoints”的点图层，其中包含“value”和“val_order”列，那么：
SELECT value, (
SELECT count(*) FROM mypoints AS my
WHERE my.value>=mypoints.value) AS val_order
FROM mypoints
ORDER BY value DESC;
Gives the descending order of the values. I can update the "val_order" column this way:
给出值的降序。我可以通过这种方式更新“val_order”列：
UPDATE mypoints SET val_order = (
SELECT count(*) FROM mypoints AS my
WHERE my.value>=mypoints.value
);
回答by F43G4N
What you are asking can be explained in two different ways, but I'm assuming you want to sort the resulting table and then number those rows according to the sort.
您所问的问题可以用两种不同的方式来解释，但我假设您想对结果表进行排序，然后根据排序对这些行进行编号。
declare @resultrow int
select
@resultrow = row_number() OVER (ORDER BY Name Asc) as 'Row Number'
from Categories WHERE CategoryID = 'C_L776102U'
select @resultrow
