选择连续记录

Question

CREATE TABLE `sequence` (`id` int(11) NOT NULL auto_increment, `load_status` varchar(100) default NULL, PRIMARY KEY  (`id`));
INSERT INTO `sequence` VALUES (1,'success'),(2,'success'),(3,'success'),(4,'fail'),(5,'success');

mysql>select * from sequence;
+----+-------------+
| id | load_status |
+----+-------------+
|  1 | success     | 
|  2 | success     | 
|  3 | success     | 
|  4 | fail        | 
|  5 | success     | 
+----+-------------+
5 rows in set (0.00 sec)

我希望能够获取所有按顺序显示“success”的状态。将返回ID为1、2和3的状态。但是，除非它后面跟着另一个“success”，否则不会选择ID为5的状态。

- shantanuo

---

你能解释一下为什么返回ID=3吗？ - ajreal

Answer 1

如果你正在处理大量数据，这种方法可能不可行（我对大型数据库缺乏经验），但也许这可以帮助你。

思路是创建子查询来获取相邻的行：

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)

- alltom

Answer 2

有些地方让我感到这个解决方案的速度有些问题，但是我认为你可能可以使用它：

SELECT current.id 
FROM sequence AS current 
LEFT JOIN sequence AS next ON(current.id = next.id+1)
WHERE (current.load_status = 'success' AND next.load_status = 'success') OR (current.load_status = 'success' AND next.load_status = 'fail') OR current.load_status = 'success';

- Nikoloff

---

快完成了，但你应该按ID排序，并且不要假设next.id+1。 - ajreal

是的，我忘记了数据不连续的情况。查询应该改为：SELECT current.id FROM sequence AS current LEFT JOIN sequence AS next ON(next.id = (SELECT id FROM sequence WHERE id > current.id ORDER BY id DESC LIMIT 1)) WHERE (current.load_status = 'success' AND next.load_status = 'success') OR (current.load_status = 'success' AND next.load_status = 'fail') OR current.load_status = 'success' ORDER BY id 或类似的内容。 - Nikoloff

但是根据他的要求，在另一个成功之前，他不想显示第5个ID。 - Developer

Answer 3

好的，我尝试了一些在不同数据集上表现良好的解决方案，但总是存在一些问题。不知道为什么我认为不需要检查前一行… 最后我想到的是这个：

SELECT current.id
FROM sequence AS current 
LEFT JOIN sequence AS next ON(next.id = (SELECT id FROM sequence WHERE id > current.id ORDER BY id DESC LIMIT 1))
LEFT JOIN sequence AS prev ON(prev.id = (SELECT id FROM sequence WHERE id < current.id ORDER BY id DESC LIMIT 1))
WHERE (current.load_status = 'success' AND next.load_status = 'success') OR (current.load_status = 'success' AND next.load_status = 'fail') OR (current.load_status = 'success' AND prev.load_status = 'success')
ORDER BY id

希望它最终解决了这个问题。

- Nikoloff

Answer 4

试试这个

SELECT * FROM sequence ORDER BY FIELD(load_status, 'Success', 'fail');

- Developer

---

这不会实现他想要的。 - Simon