mysql 组合所以_mysql查询以查找所有可能的组合，以组合开头和结尾（简化重复）...

-- 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');

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);

Original question:-

Update:-

Expected results:-

100:2000

101:2000

解决方案

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);