mysql - 检查我的表中是否有特定的名称顺序

Question

我有下表stops，如何检查以下停靠点名称顺序GHI、JKL、MNO在我的停靠点表中是否可用？

停止表:

CREATE TABLE IF NOT EXISTS stops
(
  stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  name varchar(30) NOT NULL, 
  lat double(10,6), 
  longi double(10,6)
);

简单:

1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 SDU
8 VWX

Answer 1

当存在 'GHI','JKL','MNO' 有序时，此查询将返回 1:

SELECT 1
FROM stops s1 
JOIN stops s2 ON s1.stop_id = s2.stop_id - 1
JOIN stops s3 ON s2.stop_id = s3.stop_id - 1
WHERE CONCAT(s1.name, s2.name, s3.name) = CONCAT('GHI','JKL','MNO')

SQL Fiddle Demo