MySql:查找特定记录的行号 [英] MySql: Find row number of specific record

问题描述

我正在使用一个通用数据面板，该面板可以有各种不同的查询供该面板使用.它们可能是从表或视图中选择的简单查询，也可能是用户使用复杂的联接和其他表达式定义自己的复杂查询.我正在尝试修改数据面板，以便如果用户选择了一条记录，然后对该表进行了排序，我发现该记录现在处于打开状态的页面，移至该页面，然后重新选择该记录.

I am working with a generic data panel that can have a variety of different queries feeding that panel. They might be simple queries that select from a table or view, or complex ones that users define themselves with complicated joins and other expressions. I am trying to modify my data panel so that if a user selects a record, then sorts the table, I find the page that record is now on, move to it, and then reselect the record.

我已经解决了大部分问题，但是我很难找到要移到的页码.我最初只是简单地遍历数据面板中的行，但是由于页面调度，因此页码越大，效率就越低.相反，我决定直接通过SQL进行此操作，这就是我现在遇到的问题.

I've got most of this worked out, except I am having trouble finding the page number to move to. I originally simply looped over the rows in the data panel but because of paging, this proved to be very inefficient the larger the page number became. I instead decided to do this directly through SQL which is where I am stuck now.

我决定，如果可以通过运行产生结果的相同查询来找到所选行的行号，那么我可以计算出我需要移至该页面并跳至该页面的最终页码.我接受了运行以生成结果的查询，并增加了一个变量以获取行号.

I decided that if I could find the row num of the selected row by running the same query that produced the results I could calculate the final page number that I need to move to and jump right to that page. I took the query that was run to generate the results, and incremented a variable to get the row numbers.

原始查询为

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

目前，我正在选择所有记录.然后，我包装上面的查询，并选择记录与我选择的记录匹配的最小行，

At this point I'm selecting all records. I then wrapped the above query and selected the minimum row where the record matched my selected record like so

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.

At first this appeared to be working. During testing however, I discovered that if I sorted on a field that was not sufficiently unique (e.g. 1000 records all had the same value in this field) it stopped working. I did some digging and found that the above code would return a different rownum every time the query was run.

经过一些额外的挖掘，我发现如果运行以下查询，我会得到想要的结果

After some additional digging, I found that if were to run the following query, I would get the results I wanted

select * from table_a order by column_c

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

But if I simply wrapped that query like so

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

每次我运行查询时，记录的顺序都发生了巨大变化.这解释了为什么行num之所以改变是因为实际上它在改变.但是，我无法弄清楚为什么仅通过包装查询即可更改顺序.我已经在其他数据库引擎中完成了此操作，因此我假设它与MySql特别相关，但是我无法找到信息来解释原因.我的推测是，按此类包装在查询中时，不应用order by，或者行为不符合预期.

The order of the records changed drastically every time I ran the query. This explains why the row num was changing because it is in fact changing. However, I can't figure out why the order would change simply by wrapping the query. I've done this in other database engines so I assume it has to do with MySql specifically but I have been unable to locate information to explain why. My presumption is that the order by is either not applied when wrapped in query like this, or the behavior is not as expected.

接下来，我试图像这样将rownum计数直接移到主查询/基本查询中

Next, I attempted to move the rownum count directly into the main/base query like so

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

单独运行此查询将创建正确的行号.但是，由于我需要找到所选记录的特定行号，因此必须像这样包装该查询

Running this query by itself creates the correct row numbers. However, since I need to find the specific row num of the selected record I must wrap that query like so

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 = ?

执行此操作后，顺序by似乎被忽略了，它按记录在表中出现的顺序(而不是它们在基本查询中的顺序)对事物进行计数.

As soon as I do this, the order by seems to be ignored and it counts things in the order that the records appear in the table, instead of the order they are in in the base query.

我有兴趣了解根本问题，即打包后不能正确应用数据集排序，以及潜在的其他解决方案来查找特定记录所在的页码.

I'm interested in both understanding the underlying issue with the data set sort not being applied correctly when wrapped up, as well as potential other solutions for finding the page number that a specifc record resides on.

注意，我在最终的外部查询上使用min，因为最终用户可能会选择多行，并且所有这些行都进入了最终的where子句.因此，我想找到最低的行号并移至该页面.

Note, I am using min on the final outer query because ultimately the user may select multiple rows, and all of those rows go into the final where clause. So I want to find the lowest row num and move to that page.

推荐答案

我的目的已解决:) 因此，如果有人认为合适，我会在这里发布:

My purpose is solved:) So, I am posting here if anyone finds it suitable:

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.

如果您还需要可用于确定分页偏移值的页码，则只需更改上面的第一行，如下所示:

If you need the page number too which may be used to determine the offset value for pagination, then just change the 1st line above like this:

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

您将在第1页上使用pageNumber == 0，在第2页上使用pageNumber == 1，依此类推....

You will have pageNumber==0 for page 1, and pageNumber==1 for page 2 and so on.....

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