代码之家 › 专栏 › 技术社区 › hovado

MySQL:如何在列中查找值序列

mysql

hovado · 技术社区 · 7 年前

我有一长串随机值的行:

| id | value |
|----|-------|
|  1 | abcd  |
|  2 | qwer  |
|  3 | jklm  |
|  4 | yxcv  |
|  5 | tzui  |

然后我有一个由几个值组成的数组:

array('qwer', 'jklm');

我需要知道,这个数组中的值序列是否已经以给定的顺序存在于表中。在这种情况下,存在值序列。

我尝试从表和数组中浓缩所有值,并匹配两个字符串,这对于很少的行非常有用,但实际上表中有数十万行。我认为应该有更好的解决办法。

2 回复 | 直到 7 年前

Bill Karwin 7 年前

如果列表较短,可以只进行自联接,并为每个联接的表引用详细说明条件:

select t1.id from MyTable as t1 join MyTable as t2 
where t1.value='qwer' and t2.value='jklm' and t1.id=t2.id-1;

如果没有这样的序列,则返回一个空集。当然,它假设id号是连续的(在您的示例中是连续的,但一般来说,这是一个危险的假设)。

如果你的单子太长了,这就行不通了。MySQL在一个查询中支持63个表引用,这是一个硬限制。

这里有另一个解决方案,它适用于任何大小的列表,但只有在 id 已知值是连续的:

select t1.id from MyTable as t1 join MyTable as t2 
  on t2.id between t1.id and t1.id+1
where t1.value = 'qwer' and t2.value in ('qwer','jklm')
group by t1.id
having group_concat(t2.value order by t2.id) = 'qwer,jklm';

t1行是潜在匹配行序列的开始,因此它必须匹配列表中的第一个值。

然后连接到t2行,这是可能匹配的行的完整集合。

t2行的集合也被限制为不超过N行的集合,这取决于您正在搜索的N个值列表的大小。但是SQL无法根据行数创建组,我们只能根据行中的某个值进行限制。因此,如果可以假设id值是连续的,那么这就是为什么它可以工作的原因。

Dejan Dozet 7 年前

通过这种方式,您可以对整个集合执行此操作:

select value1, value2
from
(
    select *
    from (
        SELECT [IMEPAC] value1 , ROW_NUMBER() over(order by [MATBR]) rn1
          FROM [PACM]
    ) a1 join
        (
        SELECT [IMEPAC] value2 , ROW_NUMBER() over(order by [MATBR]) rn2
          FROM [PACM]
    ) a2 on a1.rn1 = a2.rn2 + 1
) a
group by value1, value2
having count(*) > 1

它是为MS SQL编写的,但您也可以轻松地重写它以适应mysql。

我使用>IMEPAC上的400000行不是任何索引的一部分,它运行(第一次也是唯一一次)6秒。

以下是Mysql版本:

select value1, value2, count(*) count
from
(
    select *
    from (
      SELECT @row_number1:= @row_number1 + 1 AS rn1, content as value1
      FROM docs,(SELECT @row_number1:=0) AS t
      order by id
    ) a1 join
    (
      SELECT @row_number2:= @row_number2 + 1 AS rn2, content value2
      FROM docs,(SELECT @row_number2:=0) AS t
      order by id
    ) a2 on a1.rn1 = a2.rn2 + 1
) a
group by value1, value2
having count(*) > 1;

SQL Fiddle here