如果你正在处理大量数据,这种方法可能不可行(我对大型数据库缺乏经验),但也许这可以帮助你。
思路是创建子查询来获取相邻的行:
mysql> select id, load_status, (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) as prev_load_status, (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1) as next_load_status from test as t1;
+
| id | load_status | prev_load_status | next_load_status |
+
| 1 | success | NULL | success |
| 2 | success | success | success |
| 3 | success | success | fail |
| 4 | fail | success | success |
| 5 | success | fail | NULL |
+
5 rows in set (0.00 sec)
我最初在那里加了一个 HAVING
子句来限制结果为聚类:
mysql> select id, load_status, (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) as prev_load_status, (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1) as next_load_status from test as t1 having load_status = prev_load_status or load_status = next_load_status;
+
| id | load_status | prev_load_status | next_load_status |
+
| 1 | success | NULL | success |
| 2 | success | success | success |
| 3 | success | success | fail |
+
3 rows in set (0.00 sec)
但事实证明,您可以将子查询直接放在WHERE
子句中:
mysql> select id, load_status from test as t1 where load_status = (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) or load_status = (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1);
+
| id | load_status |
+
| 1 | success |
| 2 | success |
| 3 | success |
+
3 rows in set (0.00 sec)
ID=3
吗? - ajreal