获取查询的行号 [英] Getting row number for query

问题描述

我有一个将返回一行的查询.有什么办法可以在对表进行排序时找到我正在查询的行的行索引?

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?

我已经尝试过 rowid 但在我期待第 7 行时得到了 #582.

I've tried rowid but got #582 when I was expecting row #7.

例如:

    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

我尝试了 rowid 并得到了意想不到的结果:

I've tried rowid with unexpected results:

SELECT rowid FROM Categories WHERE CategoryID = 'C_L7761O2U ORDER BY Name

我也尝试过 J Cooper 的建议(如下)，但行号不正确.

I've also tried J Cooper's suggestion (below), but the row numbers just aren't right.

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

推荐答案

这里有一种在Sqlite中获取行号的方法:

Here is a way to get the row number in 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;

这篇关于获取查询的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！