MySql:查找具体记录的行号
我正在使用一个通用的数据面板，它可以有各种不同的查询提供给该面板。它们可能是从表或视图中选择的简单查询，也可能是用户使用复杂的联接和其他表达式定义自己的复杂查询。我正在尝试修改我的数据面板，以便如果用户选择了一条记录，然后对该表进行排序，我会找到该记录所在的页面，移动到该页面，然后重新选择该记录。
我已经解决了大部分问题，只是在找到要移动到的页码时遇到了麻烦。我最初只是循环遍历数据面板中的行，但由于分页的原因，页码越大，效率就越低。相反，我决定直接通过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条记录在这个字段中都有相同的值)，它就会停止工作。我做了一些调查，发现上面的代码每次运行查询时都会返回一个不同的rownum。
经过一些额外的挖掘之后，我发现如果运行以下查询，我会得到我想要的结果
select * from table_a order by column_c但是如果我像这样简单地包装查询
select * from (select * from table_a order by column_c)每次我运行查询时，记录的顺序都会发生很大的变化。这就解释了为什么行数会发生变化，因为它实际上正在发生变化。然而，我不明白为什么仅仅通过包装查询就会改变顺序。我在其他数据库引擎中也这样做过，所以我认为它特别与MySql有关，但我一直无法找到信息来解释原因。我的假设是，当包装在这样的查询中时，order by要么不适用，要么行为不符合预期。
接下来，我尝试将rownum count直接移到主/基查询中，如下所示
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子句。所以我想找到最低的行数，然后移到那一页。
回答 3
Stack Overflow用户
发布于 2014-05-22 14:32:53
我的目的是解决：)所以，如果有人觉得合适的话，我会在这里发帖：
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，依此类推……
Stack Overflow用户
发布于 2013-06-08 02:41:03
这是一个很大的问题，所以让我试着分析一下这个问题。首先，看起来你需要解决不可预知的行排序问题。
我在这里给您的建议是，始终以某种方式对表的唯一主键进行排序。如果用户正在以原始的“未排序”(即按用户选择)的顺序查看页面，则仍然使用如下排序：
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]显然，出于性能原因，您需要确保允许排序的所有字段都应该被索引。
Stack Overflow用户
发布于 2014-05-30 19:12:28
感谢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
修改后的limit查询将为：
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再次感谢，这真的拯救了我的一天！！:)
**UPDATE：**如果您的查询中有GROUP BY子句，这将不起作用。
https://stackoverflow.com/questions/16990420
复制相似问题
腾讯云开发者
