mysql 查询以查找所有可能的类似组合以开头和结尾 [英] mysql query to find all possible like combinations start with and ends with

问题描述

在Mysql中我有一个这样的表

In Mysql I have a table like this

而且我只想过滤那些可以在 groupname 列中以以下所有可能组合开头或结尾的记录.(sm.code)

And I want to filter only those records that can either start with or ends with all the below possible combinations in the groupname column. (sm.code)

196
328
2600
2708
3666
4453
4460
4468
4469

我尝试了一个查询，但在查找所有可能的组合时遇到了一些问题.

I tried a query but have some problem in finding all possible combinations.

SQL:-

select cr.groupname,cr.description,cr.higher_limit,cr.lower_limit, ce.severity from compatibility_rule as cr 
        join starting_material as sm on sm.id=cr.starting_material_id 
        join component as c on c.id=sm.component_id and c.cas_number in ('67-56-1','67-64-1')
        join compatibility_error as ce on ce.id=cr.compatibility_error_id 
        and (cr.groupname like concat('%: ', sm.code) and cr.groupname like concat(sm.code, ' : %'))  
        and c.active=true and sm.active=true and cr.active=true 
        order by cr.groupname;  

我可能需要在这部分做一些调整，但不确定如何做.

I might have to tweek something in this part, but not sure how.

and (cr.groupname like concat('%: ', sm.code) and cr.groupname like concat(sm.code, ' : %'))  

更新:-还在这里

我现在在链接下面的 SQLFiddle 上创建了一个示例

I have now created a sample on SQLFiddle below link

SQL Fiddel:http://sqlfiddle.com/#!9/54714e/39

SQL Fiddel: http://sqlfiddle.com/#!9/54714e/39

我只想过滤 sm 表中包含各种值组合的组名.即 100,101,2000.

I would like to filter only the group names that contains various combinations of values in the sm table. i.e. 100,101,2000.

下面的查询也返回其他组合，如

The below query returns other combinations too like

100:2000
100:2001
101:2000
101:2001

SQL:-

select distinct d.groupname from docs d, sm s  where d.groupname like concat(s.code,'%') or d.groupname like concat('%',s.code);

推荐答案

基于 http://sqlfiddle.com/#!9/54714e/1你可以使用这个查询

based on the schema that appears in http://sqlfiddle.com/#!9/54714e/1 you can use this query

select distinct * from (select d.id , d.groupname from docs as d,sm as s where d.groupname like (CONCAT("%",s.code)) or (CONCAT(s.code,"%"))) as t;

这篇关于mysql 查询以查找所有可能的类似组合以开头和结尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！