获取查询的行号
问题描述:
我有一个查询将返回一行。有什么方法可以找到我查询表的行索引时排序?获取查询的行号
我试过了rowid
,但是当我期待第7排时得到了#582。
如:
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意外的结果:
SELECT rowid FROM Categories WHERE CategoryID = 'C_L7761O2U ORDER BY Name
编辑:我也试过Ĵ库珀的建议(见下文),但行号刚是不对的。
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的行号:
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;
答
你问什么可以在两种不同的方式来解释,但我假设你要排序的生成表,然后根据排序对这些行进行编号。
declare @resultrow int
select
@resultrow = row_number() OVER (ORDER BY Name Asc) as 'Row Number'
from Categories WHERE CategoryID = 'C_L776102U'
select @resultrow
+1
row_number()在sqlite中不可用 – 2012-02-05 21:56:46
答
这是一个有趣的技巧,您可以在Spatialite中使用以获取值的顺序。如果使用带有WHERE子句的count()函数来限制> =当前值,那么计数实际上会给出顺序。所以,如果我有一个名为“mypoints”同列“价值”和“val_order”,然后点图层:
SELECT value, (
SELECT count(*) FROM mypoints AS my
WHERE my.value>=mypoints.value) AS val_order
FROM mypoints
ORDER BY value DESC;
给出的值的降序排列。 我可以更新 “val_order” 一栏是这样的:
UPDATE mypoints SET val_order = (
SELECT count(*) FROM mypoints AS my
WHERE my.value>=mypoints.value
);
为什么要修改我的查询'... WHERE t2.RecipeID 2012-02-08 22:06:02