我有以下数据(fiddle),
id | datec | event |
---|---|---|
1 | 2022-09-19 12:16:38 | EVENTA |
2 | 2022-09-19 12:16:38 | A |
3 | 2022-09-19 12:21:08 | B |
4 | 2022-09-19 12:21:12 | EVENTD |
5 | 2022-09-19 12:25:18 | C |
6 | 2022-09-19 12:25:18 | D |
7 | 2022-09-19 12:25:28 | E |
8 | 2022-09-19 12:25:29 | F |
9 | 2022-09-19 12:25:38 | EVENTA |
10 | 2022-09-19 12:25:39 | G |
11 | 2022-09-19 12:25:40 | H |
12 | 2022-09-19 12:25:48 | I |
13 | 2022-09-19 12:27:18 | EVENTD |
14 | 2022-09-19 12:29:08 | J |
我不知道如何在其他两个值之间进行 Select ,但要按特定的顺序进行 Select .只应按该顺序返回事件between EVENTA and EVENTD.
因此,结果应该是ID为1到4和9到13的行
try 执行类似下面的操作,但它给了我id 1、4、9和13,省略了它们之间的内容.
SELECT id, datec, event
FROM table1
WHERE event BETWEEN 'EVENTA' AND 'EVENTD';
然后我试着用这个,
SELECT id, datec, event
FROM table1
WHERE (id BETWEEN (SELECT id
FROM table1
WHERE event BETWEEN 'EVENTA' AND 'EVENTD'
LIMIT 1)
AND (SELECT id
FROM table1
WHERE event BETWEEN 'EVENTA' AND 'EVENTD'
LIMIT 1,1))
OR (id BETWEEN (SELECT id
FROM table1
WHERE event BETWEEN 'EVENTA' AND 'EVENTD'
LIMIT 2,1)
AND (SELECT id
FROM table1
WHERE event BETWEEN 'EVENTA' AND 'EVENTD' LIMIT 3,1));
它给出了结果,但我的表中有很多行.
能不能请谁来指导我如何把这个重复到最后,因为我确信有办法做到这一点,但我想不出该怎么做?
向您致敬,
皮埃尔