我有以下數(shù)據(jù)(小提琴),
id | datec | 事件 |
---|---|---|
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 | #我 |
13 | 2022-09-19 12:27:18 | #EVENTD |
#14 | 2022-09-19 12:29:08 | J |
我不知道如何在另外兩個(gè)值之間選擇值,但要按照特定的順序。僅應(yīng)按順序傳回 EVENTA 和 EVENTD 之間的事件。
因此結(jié)果應(yīng)該是 ID 為 1 到 4 和 9 到 13 的行
嘗試執(zhí)行類似以下操作,但它給了我 id 1,4,9 和 13,省略了它們之間的內(nèi)容。
SELECT id, datec, event FROM table1 WHERE event BETWEEN 'EVENTA' AND 'EVENTD';
然後我嘗試使用這個(gè),
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));
它給了我結(jié)果,但我的表中有很多行。
請(qǐng)有人指導(dǎo)我如何重複這個(gè)動(dòng)作直到最後,因?yàn)槲掖_信有辦法做到這一點(diǎn),但我不知道如何做?
問候,
皮埃爾
這是一種方法:
此時(shí)您應(yīng)該注意到,當(dāng)我們的armed_event分區(qū)中還沒有EventD時(shí),我們產(chǎn)生的排名值假定為0。當(dāng)找到第一個(gè)EventD時(shí),它的值為1,直到找到下一個(gè)EventD。
因此,當(dāng)此排名值為 0 或 1 並且事件恰好是「EventD」時(shí),您可以在 WHERE
子句中進(jìn)行相應(yīng)的過濾。
WITH cte AS ( SELECT *, SUM(`event`='EVENTA') OVER(ORDER BY datec, id) AS armed_events, SUM(`event`='EVENTD') OVER(ORDER BY datec, id) AS disarmed_events FROM Table1 ), cte2 AS ( SELECT *, DENSE_RANK() OVER(PARTITION BY armed_events ORDER BY disarmed_events) -1 AS rn FROM cte ) SELECT `id`, `datec`, `event` FROM cte2 WHERE rn = 0 OR (rn = 1 AND `event` = 'EVENTD') ORDER BY id
輸出:
id | datec | 事件 |
---|---|---|
1 | 2022-09-19 12:16:38 | 活動(dòng) |
2 | 2022-09-19 12:16:38 | 一個(gè) |
3 | 2022-09-19 12:21:08 | B |
4 | 2022-09-19 12:21:12 | 事件 |
9 | 2022-09-19 12:25:38 | 活動(dòng) |
10 | 2022-09-19 12:25:39 | G |
11 | 2022-09-19 12:25:40 | H |
12 | 2022-09-19 12:25:48 | 我 |
13 | 2022-09-19 12:27:18 | 事件 |