MySQL根据上一行和下一行选择行

Question

假设我有一张看起来像这样的 table :

          mytable
category |      begintime
---------|----------------------
cat1     | 2016-09-25 15:00:00
cat2     | 2016-09-25 16:00:00
cat1     | 2016-09-25 17:30:00
cat3     | 2016-09-25 19:00:00
cat1     | 2016-09-25 20:00:00
  :                 :

请注意，它没有 ID 号，begintime 列是我的主键。

最后，我想选择所有被某个类别包围的行，即选择所有行，使得上一行的类别为 @catBefore 并且类别下一行是 @catAfter。

例如，我想要的是这样的:

SELECT * FROM mytable WHERE previousRow.category = 'cat1' AND nextRow.category = 'cat1'

导致

    SELECT * FROM ...
category |      begintime
---------|----------------------
cat2     | 2016-09-25 16:00:00
cat3     | 2016-09-25 19:00:00
  :                 :

这里面的previousRow和nextRow，好像是不可定义的。

想法

我已经尝试了一些东西，但还没有任何结果。我的一个想法是首先选择上一个和下一个类别作为新列，比如:

SELECT mytable.*, 
       previousRow.category AS prevCat, 
       nextRow.category AS nextCat 
FROM mytable, [stuff-I-don't-know]

导致

     SELECT mytable.* ...
category |      begintime       | prevCat | nextCat
---------|----------------------|---------|---------
cat1     | 2016-09-25 15:00:00  | null    | cat2
cat2     | 2016-09-25 16:00:00  | cat1    | cat1
cat1     | 2016-09-25 17:30:00  | cat2    | cat3
cat3     | 2016-09-25 19:00:00  | cat1    | cat1
cat1     | 2016-09-25 20:00:00  | cat3    | ...
  :                 :

然后使用 WHERE 子句进行过滤。

这个想法是否可行，或者可以通过其他方式实现吗？

Answer 1

一种方法使用相关子查询。这在性能方面应该没问题，如果您确实有主键声明:

select t.*
from (select t.*,
             (select t2.category
              from mytable t2
              where t2.begintime < t.begintime
              order by begintime desc
              limit 1
             ) as prev_category,        
             (select t2.category
              from mytable t2
              where t2.begintime > t.begintime
              order by begintime asc
              limit 1
             ) as next_category
  from mytable t
 ) t
where prev_category = @cat1 and next_category = @cat2;

编辑:

你可以用变量来做到这一点:

select t.*
from (select t.*,
             (@pn := (case when (@pcy := @pn) = NULL then -1 -- never gets here 
                           when (@pn := category) = NULL then -1 -- never gets here
                           else @pcy
                      end)
                   ) as next_category
      from (select t.*,
                   (@pc := (case when (@pcx := @pc) = NULL then -1 -- never gets here 
                                 when (@pc := category) = NULL then -1 -- never gets here
                                 else @pcx
                            end)
                   ) as prev_category
            from t cross join
                 (select @pc := '') params
            order by t.begintime
           ) t cross join
           (select @pn := '') params
      order by t.begintime desc
     ) t
where prev_category = @cat1 and next_category = @cat2;