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