mysql - 如何不喜欢 MySQL 中的很多项目？

Question

很多东西都不喜欢，不是绅士风度。但是我需要一个查询，该查询使用 not like 的许多项目列表来exclude 许多项目(not in)。

| source |
|========|
| danny  |
| ram    |
| nach   |
| boom   |
| trach  |
| banana |

| key_exclude    |
|================|
| danny          |
| ram            |

| like_exclude   |
|================|
| bo             |
| tr             |

预期结果:

| result |
|========|
| banana |
| nach   |

我想要这样的东西:

select * from source where key not in (select key from key_exclude.key) 
  and key not like in(like_exclude.key)

但是没用

我能做到:

select * from source where key not in (select key from key_exclude.key) 
  and key not like '%bo%' and key not like '%tr%'

唯一的问题是“like_exclue”可能包含数百条记录。

Answer 1

这是一个使用子查询的查询解决方案:

select * 
from source AS s
where
    s.`key` not in( select k.`key` from key_exclude AS k ) AND
    NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));

下面是一个经过数据验证的完整演示，也由 OP http://sqlfiddle.com/#!9/22fe8a/1/0 放在 SQLFiddle 上

SQL:

-- Data
create table source(`key` char(20));
insert into source values
( 'danny' ),
( 'ram' ),
( 'nach' ),
( 'boom' ),
( 'trach' ),
( 'banana' );

create table key_exclude( `key` char(20));
insert into key_exclude values
( 'danny' ),
( 'ram' );

create table like_exclude( `key` char(20) );
insert into like_exclude values
( 'bo' ),
( 'tr' );

-- SQL Needed
select * 
from source AS s
where
    s.`key` not in( select k.`key` from key_exclude AS k ) AND
    NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));

输出:

mysql> select *
    -> from source AS s
    -> where
    -> s.`key` not in( select k.`key` from key_exclude AS k ) AND
    ->     NOT EXISTS( select 1 from like_exclude l WHERE s.`key` LIKE CONCAT('%', l.`key`, '%'));
+--------+
| key    |
+--------+
| nach   |
| banana |
+--------+
2 rows in set (0.00 sec)

实时示例 - SQL Fiddle:http://sqlfiddle.com/#!9/22fe8a/1/0