MySQL获取每列记录中的子串匹配计数

问题描述：

我有一个包含6个字符串（varchar（6））的列（代码）的mysql表。该列的示例记录是：MySQL获取每列记录中的子串匹配计数

ID  code 
1  ADHNQS 
2  BDHLQS 
3  AEGMQS

我需要做的是在一个时间比较1码记录到休息和恢复“相关的代码”。如果相关代码包含4个或更多相同字符，则该代码将符合条件。例如：

ADHNQS - > BDHLQS将是一个匹配，因为D，H，Q，S是共享的，并且是4或更大。

ADHNQS - > BCHLQR不会是一个比赛，因为他们共同的字符小于4

我怎样才能与他们的性格匹配计数SQL检索一组的所有记录，他们是4或更高？我研究了许多MYSQL字符串函数，但没有发现任何可以作为简单解决方案跳出来的东西。预先感谢您的帮助！

你确定你想这样做在SQL查询，而不是一个标准的编程语言？ – Jay 2014-09-05 19:21:20

我宁愿在sql中比在php中执行它，是的。 – 2014-09-05 19:27:02

有字符串函数来查看每个字符（SUBSTRING） - 但是你必须为每个4个匹配的排列创建一个CASE - 并且有很多。否则，你会在脚本语言中更好地在MYSQL之外做它。 – user3741598 2014-09-05 19:30:23

答

如果你只是有你的参考行的ID（这里：1）：

SELECT b.ID, b.code, 
(if(substring(b.code,1,1)=substr(a.code,1,1),1,0) + if(substring(b.code,2,1)=substr(a.code,2,1),1,0) + if(substring(b.code,3,1)=substr(a.code,3,1),1,0) + if(substring(b.code,4,1)=substr(a.code,4,1),1,0) + if(substring(b.code,5,1)=substr(a.code,5,1),1,0) + if(substring(b.code,6,1)=substr(a.code,6,1),1,0)) as matchcount 
FROM yourtablename as a, yourtablename as b 
WHERE a.ID=1 
AND b.ID<>a.ID 
GROUP BY 1 
HAVING matchcount>=4 
ORDER BY matchcount desc

ID code matchcount 
2 BDHLQS 4

如果你只需要代码（位置：ADHNQS）那么你可以像这样手动建立你的查询（如果存在，将返回你的确切代码）：

SELECT ID, code, 
(if(substring(code,1,1)="A",1,0) + if(substring(code,2,1)="D",1,0) + if(substring(code,3,1)="H",1,0) + if(substring(code,4,1)="N",1,0) + if(substring(code,5,1)="Q",1,0) + if(substring(code,6,1)="S",1,0)) as matchcount 
FROM yourtablename 
GROUP BY 1 
HAVING matchcount>=4 
ORDER BY matchcount desc

退货：

ID code matchcount 
1 ADHNQS 6 
2 BDHLQS 4

非常简单的答案，遵循，并完美工作。谢谢！ – 2014-09-05 20:22:50

答

在示例“匹配”中，匹配的字符在两个字符串中都处于相同的位置。目前尚不清楚这是否是实际的规格，或者如果这只是示例中的异常情况。另外，我们注意到，在示例数据中，字符列表是不同的，任何字符串中都没有两个相同的字符。再次，不确定这是规范的一部分，还是示例中的异常。

此外，代码值的长度始终是六个字符？对较短的字符串或空格字符进行特殊处理？等

在最简单的情况下，如果我们通过排名来比较字符串位置，唯一的要求就是一个字等于另一个字符（无特殊处理的空间，或者非字母，等），那么像这样将返回指定的结果：

SELECT c.id 
    , c.code 
    , d.id 
    , d.code 
    FROM mytable c 
    JOIN mytable d 
    ON d.id <> c.id 
    AND (IFNULL(NULLIF(SUBSTR(c.code,1,1),'') = NULLIF(SUBSTR(d.code,1,1),'') ,0) 
     + IFNULL(NULLIF(SUBSTR(c.code,2,1),'') = NULLIF(SUBSTR(d.code,2,1),'') ,0) 
     + IFNULL(NULLIF(SUBSTR(c.code,3,1),'') = NULLIF(SUBSTR(d.code,3,1),'') ,0) 
     + IFNULL(NULLIF(SUBSTR(c.code,4,1),'') = NULLIF(SUBSTR(d.code,4,1),'') ,0) 
     + IFNULL(NULLIF(SUBSTR(c.code,5,1),'') = NULLIF(SUBSTR(d.code,5,1),'') ,0) 
     + IFNULL(NULLIF(SUBSTR(c.code,6,1),'') = NULLIF(SUBSTR(d.code,6,1),'') ,0) 
     ) >= 4 
WHERE c.id = 1 
ORDER BY c.id, d.id

如果我们需要每一个字符code比较各自在对方code的人物，我们就会有类似的东西，我们刚刚需要执行总共36次比较。（比较位置1至位置 1,2,3,4,5,6，比较位置2至位置1,2,3,4,5,6）

这可以与查询完全相同除了谓词将包含总共36个比较测试，而不仅仅是6个。

这会再次引发字符串中同一字符的倍数问题，以及如何将这些字符“计算”为匹配。例如，考虑：

code1: QbQdef 
code2: QxyQQz

将q在编码1 1位置码2匹配三问的，并在编码1的2位Q也会匹配三问在代码2 ...为6总比赛数。我们是否想将这两个代码视为匹配？如果没有，我们可以稍微修改查询中的测试块，以便将位置1中的字符与代码2中的任何字符进行匹配，只会将1添加到匹配计数中。

为了确定实现所需结果的实际SQL语句，需要充实更多的规范。

答

@ spencer7593有一个非常长的漂亮的sql语句，当varchar位置相同时工作。

，但如果你想解决这个问题，PHP和焦炭位置并不总是相同的：

$string1 = 'SOMESTRING'; 
$stringAsArray = str_split($string1); 
sort($stringAsArray); 

$string2 = "ASDFOMKHRG"; 
$string2AsArray = str_split($string2); 

$count = 0; 
foreach($stringAsArray as $value){ 
    foreach($string2AsArray as $value2){ 
     if($value == $value2) count+=1; 
    } 
} 
if(count >= 4) return string2;

注：与这虽然一个问题是，如果字符串有重复的字符。必须有一些额外的逻辑。没有显示的问题，所以我没有添加它。

谢谢，我特别寻找SQL解决方案。我已经能够在PHP中做到这一点：）虽然感激！ – 2014-09-05 20:15:22

答

这看起来很有趣，所以我给了它一个镜头。首先，我建立了一个数字表，字面上只有1-15个数字。然后使用该表将分割字符串及其索引。

Base table

然后我加入，为自己和寻找的4个或更多的比赛。从理论上讲，只要你的数字表足够大，这应该适用于任何字符串长度。

select a2.code 
from 
    (
    select *, 
     SUBSTRING(t.code, n.num, 1) as 'Character' 
    from numbers n 
    join test t 
     on length(t.code) >= n.num 
) a1 
join 
    (
    select *, 
     SUBSTRING(t.code, n.num, 1) as 'Character' 
    from numbers n 
    join test t 
     on length(t.code) >= n.num 
    ) a2 on a1.character = a2.character and a1.id <> a2.id 
where a1.id = 1 
group by a2.code having count(1) >= 4

这里的SQL Fiddle Demo

MySQL获取每列记录中的子串匹配计数

相关推荐