mysql 记录顺序_mysql – 选择顺序记录

如果您正在使用大量数据,这种方法可能不可行(我对大型数据库缺乏经验),但这可能会有所帮助.

我们的想法是创建子查询以获取相邻的行：

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)