查询中记录的行号 [英] Row number of a record in a query

问题描述

我需要在查询中找到记录的页码.我有偏移量和限制，但还需要行号来计算.

I need to find the page number of a record in a query. I have offset and limit, but will also need the row number to calculate this.

给定一个查询对象和一个记录 ID，我如何找到行号?

Given a query object, and a record ID, how could I find the row number?

行号应该是相对于查询表的.之后将应用限制和偏移量.

row number should be relative to the query table. limit and offset will be applied afterwards.

感谢任何帮助...

推荐答案

找了很久，6 小时后找到了解决方案.

I've looked long and hard, and after 6hrs found a solution.

我使用 Mysql，所以我相信我能做到这一点的唯一方法是使用 @ 变量.

I use Mysql so I believe the only way I can do this is with @ variables.

这是我在 SQLAlchemy 中实现它的方法:

Here is how I implemented it into SQLAlchemy:

qry = ...
sel = select([qry.c.id, "@rownum := @rownum + 1 as rownumber"])
conn = sel.engine.connect()
conn.execute("SET @rownum:=0")
res = conn.execute(sel)

@rownum 将随着每一行增加.如果我们愿意，我们可以在第二行从 qry 添加更多列.res 是带有 rownumber 列的最终表，我们可以在其中调用 res.fetchall() 返回一个列表

@rownum will increment with each row. we can add more columns from qry on the 2nd line if we wanted to. res is the final table with the rownumber column, where we can call res.fetchall() to return a list

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