mysql - 获取名称在列中的顺序

Question

当 行为中存在 stop_name 的以下顺序 ABC、DEF、GHI 时，我尝试获取 stop_name DEF 表，其中behaviour_id不递增1，并且该名称在表中存在多次，而且并不总是存在一次。我怎样才能用mysql查询得到它？

简单:

1  Tree
3  Tree     
5  ABC
8  ABC
9  DEF
11 DEF
13 DEF
17 GHI
21 Moon

表:

CREATE TABLE IF NOT EXISTS behaviour(
    behaviour_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mac VARCHAR(30) NOT NULL,
    stop_name VARCHAR(30) NOT NULL,    
    stop_distance INT(11) NOT NULL, 
    speed INT(11) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

当 id 递增 1 时，此查询有效

SELECT
    b3.behaviour_id
FROM
    behaviour b1
    INNER JOIN behaviour b2 ON b1.behaviour_id = b2.behaviour_id - 1
    INNER JOIN behaviour b3 ON b2.behaviour_id = b3.behaviour_id - 1
WHERE
    CONCAT(b1.stop_name, b2.stop_name, b3.stop_name) = CONCAT('ABC', 'DEF', 'GHI')
    AND b1.mac = '12:EF:34:DF:89:JH';

Answer 1

类似于

select b1.id i1,b2.id i2,b3.id i3 FROM behav b1 
INNER JOIN behav b2 ON b2.name='DEF' AND b2.id>b1.id
INNER JOIN behav b3 ON b3.name='GHI' AND b3.id>b2.id
WHERE b1.name='ABC'

应该可以工作并给出以下结果:

i1  i2  i3
5   9   17
8   9   17
5   11  17
8   11  17
5   13  17
8   13  17

当然，如果您只想第一次出现，那就这样做

select b1.id i1,b2.id i2,b3.id i3 FROM behav b1 
INNER JOIN behav b2 ON b2.name='DEF' AND b2.id>b1.id
INNER JOIN behav b3 ON b3.name='GHI' AND b3.id>b2.id
WHERE b1.name='ABC' ORDER BY i1,i2 LIMIT 1