Can pattern matching be used in MySQL for consecutive rows?-CodePudding

I have a very simple table, similar to:

id  | val
----------
1   | 20
2   | 22
3   | 29
4   | 21
5   | 14
6   | 9
7   | 15

I want to be able to identify a pattern in consecutive rows. For example, if the input is "29, 21, 14", there are three consecutive rows that match and I want to return:

id  | val
----------
3   | 29
4   | 21
5   | 14

If the rows are not consecutive there is no match.

My attempt, which does not work:

SELECT id as firstid, val from tbl100 WHERE `val` = '29' AND firstid 1 = '21' AND firstid 2 = '14'

The problem is obviously that my query finds val 29, then looks for id 21 and id 14, it should look for val 21 and val 14, but I have no idea how to do that.

Any help appreciated!

CodePudding user response：

You can first find the matching rows and then check for sequential ids with lead and lag:

with m_vals as (
   select t1.* from t t1 join i t2 on t1.val = t2.val
)
select t3.id, t3.val from (select m.*, lag(m.id) over (order by m.id) l, lead(m.id) over (order by m.id) t from m_vals m) t3
where (t3.l is null or t3.l   1 = t3.id) and (t3.t is null or t3.t - 1 = t3.id)