MySQL查询满足数值连续序列的记录

Question

如何选择ID1值连续（两个或更多）且城镇匹配的记录

我的表格

TOWN    ID1
town1   1
town1   2
town1   4 
town2   1
town2   5 
town2   8 
town3   1 
town3   2 
town3   3 

所需的结果

TOWN    ID1
town1   1
town1   2
town3   1 
town3   2 
town3   3 

sql fiddle

http://sqlfiddle.com/#!2/b409f/26

- Santosh Pillai

---

town2 1 不也会被列出吗？ - Orbling

当然可以。它有1。也就是说，连续ID的长度为1。就像town3的第一行一样。 - Bohemian

如果ID序列不是从1开始，那么2-3-4算吗？ - Bohemian

是的，2-3-4计数。town2有ID（1,5,8），但这些都不是连续的。 - Santosh Pillai

@Orbling 抱歉，我应该在我的示例中删除 town2，1 以使其更清晰。感谢您的关注。 - Santosh Pillai

Answer 1

您可以使用EXISTS子句来检查序列中的下一个值。此代码仅匹配长度>=2的“序列”，这似乎是您从示例中想要的内容。

SELECT * 
  FROM Table1 a 
 WHERE EXISTS (SELECT * 
                 FROM Table1 b 
                WHERE b.TOWN=a.TOWN 
                  AND b.ID1 IN (a.ID1 - 1, a.ID1 + 1))
 ORDER BY TOWN, ID1

- Dan

---

虽然我认为他需要指定两个或更多项的顺序，因为这似乎是他想要的，但对于他所要求的内容来说，它运行良好。 - Orbling

@Orbling 我从他的示例中推断出来。上面的代码只会匹配两个或更多项的序列。编辑以澄清。 - Dan

@Dan 谢谢。这个方法可行。我已经使用你的解决方案更新了sqlfiddle。 - Santosh Pillai

但这将匹配一个城镇的多个序列，例如town3 1-2-3和7-8-9。我希望那就是他想要的。 - Bohemian

@SantoshPillai，也许你有误解。如果同一个城镇有两个序列，那么这个城镇的两个序列都会输出。看起来你可能只想要第一个序列。 - Bohemian

Answer 2

如果您的问题是“给我所有城镇中具有相邻id1字段的行”，那么简单地执行以下操作：

select distinct t1.*
from Table1 t1
join Table1 t2 on t2.town = t1.town and ABS(t1.ID1 - t2.ID1) = 1
order by 1, 2

请参见SQLFiddle了解更多信息。

---

如果还需要匹配另一列，请在连接条件中添加相应的条件，例如：

select distinct t1.*
from Table1 t1
join Table1 t2
  on t2.town = t1.town
  and t2.state = t1.state
  and ABS(t1.ID1 - t2.ID1) = 1
order by 1, 2

- Bohemian

---

很高兴你加了 distinct。;-) - Orbling

如何使查询匹配第二列？ - Santosh Pillai

连接操作比基于存在的查询快了数个数量级。要在另一个列上进行连接，只需将条件添加到连接中即可 - 请参见编辑后的答案。 - Bohemian

@Bohemian。感谢您的查询，以匹配另一列。 - Santosh Pillai