根据 MySQL 中的特定条件选择“下一个"行 [英] Selecting the 'next' row given specific criteria in MySQL

问题描述

我有这组数据，需要根据下一个"逻辑行中的内容进行查询.

I have this set of data and need to base my queries on what is in the 'next' logical row.

 id |  area |  order
 1     front   1
 2     back    2
 3     left    3
 4     middle  4
 5     right   5

我有一个这样的查询来计算前面区域后面跟着后面区域的实例数量

I had a query such as this to count the number of instances where a front area was followed by back area

SELECT 
    (SELECT COUNT(id) 
     FROM table AS t2 
     WHERE t2.area = 'back' AND t2.order = (t1.order +1)) AS c,  
FROM table AS t1
WHERE t1.area = 'front'

但是，数据集现在已经改变，可能看起来像这样

But, the data set has now changed and could look something like this

 id |  area |  order
 1     front   1
 2     back    3
 4     left    4
 6     middle  7
 9     right   9

现在ID和顺序没有增加，一些数据被删除然后重新添加.

Now the IDs and order are not incrementing, some data has been removed and then re-added.

给定这个新数据集，我如何再次编写相同的查询?

How can I write the same query again given this new data set?

推荐答案

您的原始查询相当聪明.这是一种稍微不同的方法.它获取子查询中的下一个区域(使用您的示例中的相关子查询).然后计算条件为真的行数:

Your original query is rather clever. Here is a slightly different approach. It gets the next area in a subquery (using a correlated subquery as in your example). It then counts the rows where the conditions are true:

select sum(case when area = 'Front' and nextarea = 'Back' then 1 else 0 end)
from (SELECT t1.*,
             (SELECT t2.area 
              FROM table t2 
              where t2.order > t1.order
              order by t2.order
              limit 1
             ) as nextarea 
FROM table t1;

这个查询比你的贵.您可以在 order 列上使用一个很好的相等条件.在这里，需要使用 limit 进行排序以获取下一个值.(order, area) 上的复合索引应该有助于提高性能.

This query is more expensive that yours. You were able to use a nice equality condition on the order column. Here, a sort is needed with the limit to get the next value. A composite index on (order, area) should help performance.

这篇关于根据 MySQL 中的特定条件选择“下一个"行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！