MySql：查找特定记录的行号

Question

我正在处理一个通用的数据面板，可以有各种不同的查询提供给该面板。它们可能是简单的从表或视图中选择的查询，也可能是用户自定义的具有复杂连接和其他表达式的查询。我试图修改我的数据面板，使得如果用户选择了一条记录，然后对表进行排序，我可以找到该记录现在所在的页面，跳转到该页面，然后重新选择该记录。

我已经解决了大部分问题，但是我在寻找要移动到的页码时遇到了麻烦。最初，我只是循环遍历数据面板中的行，但由于分页，这变得非常低效。相反，我决定直接通过SQL来解决这个问题，但现在卡在那里了。

我决定运行与产生结果的查询相同的查询，以找到所选行的行号，从而计算出我需要移动到的最终页码，并直接跳转到该页。我取出了生成结果的查询，并递增一个变量来获取行号。

原始查询如下：

select *
from table_a
order by column_c desc

更改后带有行号的查询语句如下：

select *, (@rownum := @rownum + 1) as rownum
from
(select @rownum := 0) rn
, (
  select *
  from table_a
  order by column_c desc
) data

在这个阶段，我正在选择所有记录。然后，我用上面的查询语句进行包装，并选择了符合我的选定记录的最小行，如下所示。

select min(rownum)
from
(  
  select *, (@rownum := @rownum + 1) as rownum
  from
  (select @rownum := 0) rn
  , (
    select *
    from table_a
    order by column_c desc
  ) data
) wrapper
where
  primarykeyfield1 = ?
  and primarykeyfield2 = ?

起初，这似乎很有效。然而，在测试过程中，我发现如果我按一个不够唯一的字段排序（例如，1000条记录在该字段中都有相同的值），它就无法工作。我进行了一些挖掘，发现上面的代码每次运行查询时会返回不同的行号。

经过进一步挖掘，我发现如果运行以下查询，我将获得想要的结果

select * from table_a order by column_c

但如果我只是这样包装查询

select * from (select * from table_a order by column_c)

每次运行查询时，记录的顺序都发生了巨大变化。这就解释了为什么行号会改变，因为它实际上正在改变。然而，我无法理解为什么仅仅将查询包装起来就会改变顺序。我在其他数据库引擎中做过这样的操作，所以我认为这可能与MySql有关，但是我一直没有找到相关信息来解释原因。我的假设是，在像这样的查询中包含order by时，其排序可能不会被应用，或者行为不如预期。

接下来，我尝试将行号计数直接移动到主/基本查询中，如下所示：

select *, (@rownum := @rownum + 1) as rownum
from (select @rownum := 0) rn, table_a
order by column_c desc

运行这个查询本身可以创建正确的行编号。然而，由于我需要找到所选记录的特定行号，我必须像这样包装该查询。

select min(rownum)
from (
  select *, (@rownum := @rownum + 1) as rownum
  from (select @rownum := 0) rn, table_a
  order by column_c desc
) data
where
  primarykeyfield1 = ?
  and primarykeyfield2 = ?

一旦我这样做，order by 似乎被忽略了，它按照记录在表中出现的顺序计数，而不是按照基本查询中的顺序。
我对理解数据集排序未正确应用时的潜在问题以及查找特定记录所在页面的其他解决方案都很感兴趣。
请注意，我在最终外部查询中使用min，因为最终用户可能会选择多行，并且所有这些行都进入最终的where子句。因此，我想找到最低的行号并移动到该页。

- user2464490

---

考虑简化你的问题。没有人想阅读那么多！ - Strawberry

请提供您的数据库架构以及最好附带一些示例数据。 - Bad Wolf

Answer 1

我的目的已经实现 :) 所以，我在这里发布，如果有人觉得合适：

SELECT d.myRowSerial
FROM (
    SELECT *, @rownum:=@rownum + 1 AS myRowSerial 
    FROM myTable, (SELECT @rownum:=0) AS nothingButSetInitialValue 
    WHERE 1=1 -- Optional: filter if required, otherwise, omit this line;
    ORDER BY AnyColumn -- Apply the order you like; 
) d
WHERE d.myColumn = 'Anything'; -- If you like to limit it to only
-- for any specific row(s), similar to the *MAIN query.

如果您需要页面编号，它可以用于确定分页的偏移值，则只需按照以下方法更改上面的第一行：

SELECT d.myRowSerial, FLOOR((d.myRowSerial-1)/10) AS pageNumber
-- Say, 10 is per page;

您将在第1页拥有pageNumber == 0，而在第2页，pageNumber == 1，以此类推……

- Reza Mamun

Answer 2

这是很多需要理解的内容，让我试着分解这个问题。首先，似乎您需要解决不可预测的行顺序问题。

我在这里的建议是，您总是以某种方式对表格的唯一主键进行排序。如果用户以原始“未排序”（即用户选择的未排序）顺序查看页面，则仍使用类似于以下的排序：

SELECT *
FROM table
ORDER BY primary_key ASC
LIMIT 0, [page limit value]

当用户选择按其他字段排序时，请确保主键字段仍然参与排序，如下所示：

SELECT *
FROM table
ORDER BY sort_field [ASC|DESC], primary_key ASC
LIMIT 0, [page limit value] 

这将确保在用户排序的字段的基数较低（即不同值不多）时，即使多次调用相同的排序，您也始终具有唯一的主键字段来指定排序顺序，当排序字段中的值相等时。

现在，我们来解决能够直接转到选定行和页面并更改排序的问题。我假设您知道所选行的主键并且可以在查询中使用它。

您可以首先按以下方式确定此行在新排序中的偏移量：

SELECT count(*)
FROM table
WHERE
  sort_field <= (SELECT sort_field FROM table WHERE primary_key = ?)
  AND primary_key < ?

这告诉您数据集中选定行之前（即偏移量）的行数，注意这是升序排序。如果是降序排序，您显然需要使用>=和>。

然后，您可以像下面显示的查询一样使用此偏移量：

SELECT *
FROM table
ORDER BY sort_field [ASC|DECS], primary_key ASC
LIMIT [offset], [page limit value]

如果您想要行所在的实际页面编号（例如，如果您想要固定页面的概念（其中您选择的行可能是显示的第一行或不是第一行），您还可以通过将偏移量除以页面限制值并四舍五入结果来轻松计算它。然后，您将“偏移页”乘以“页面限制”以确定要使用的偏移值。

因此，假设您的行在总偏移量为125且页面限制为50的位置。以下是伪代码：

selected_row_offset = 125;
page_limit = 50;
offset_page = floor(selected_row_offset/page_limit);
query_offset = offset_page * page_limit;

你的查询将会是

SELECT *
FROM table
ORDER BY sort_field [ASC|DECS], primary_key ASC
LIMIT [query_offset], [page limit value]

显然出于性能方面的考虑，您需要确保所有允许排序的字段都被索引。

- Mike Brant

Answer 3

感谢Reza Mamun， 我已经想出了一种方法，可以将记录编号放置在一个位置，即使您使用limit获取数据，也可以确切地知道该记录对应的编号。

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=0) AS nothingButSetInitialValue
LIMIT 0,10

这将给你记录编号。 现在要实现限制，例如传入相同的限制LIMIT 10,10，然后在FROM子句中将rownum设置为限制的起始值，即10。

限制的修改查询如下：

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=10) AS nothingButSetInitialValue
LIMIT 10,10

接下来的记录集将是：

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=20) AS nothingButSetInitialValue
LIMIT 20,10

再次感谢，这真的救了我的一天！！:)

**更新：**如果查询中有GROUP BY子句，则此方法将不起作用。

- Kanad Godse