mysql查询以查找所有可能的组合，以组合开头和结尾(简化的重复项) [英] mysql query to find all possible like combinations start with and ends with (simplified duplicate)

问题描述

-- Sample data, borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) NOT NULL auto_increment,
  `groupname` varchar(100),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `sm` (
  `id` int(6) NOT NULL auto_increment,
  `code` varchar(100),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`groupname`) VALUES
  ('100:2000'),
  ('100:2001'),
  ('101:2000'),
  ('101:2001'),
  ('102:2002'),
  ('103:2003');
INSERT INTO `sm` (`code`) VALUES
  ('100'),
  ('101'),
  ('2000');

相同的SQL Fiddle: http://sqlfiddle.com/#!9/54714e/39

SQL Fiddle of same: http://sqlfiddle.com/#!9/54714e/39

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

In MySQL, 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);

原始问题:-

mysql查询到找到所有可能的组合，都以

更新:-

预期结果:-

100:2000
101:2000

推荐答案

我刚刚将您的or条件分解为多个具有如下表的join，以实现您的预​​期结果，

I've just broke your or condition into make multiple join with the same table like below to achieve your expected result,

select distinct d.groupname from docs d
join sm s1 on
d.groupname like concat(s1.code,'%')
join sm s2 on
d.groupname like concat('%',s2.code);

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