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

不喜欢许多东西是不礼貌的。但我需要一个查询，它使用一个包含许多使用exclude的项的列表来not in许多项（not like）。

| 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”可能包含数百条记录。

最佳答案

下面是使用子查询的解决方案查询：

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`, '%'));

下面是一个经过数据验证的完整演示，也可以通过OPhttp://sqlfiddle.com/#!9/22fe8a/1/0
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小提琴：http://sqlfiddle.com/#!9/22fe8a/1/0

关于mysql - 如何不喜欢MySQL中的许多项目？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35910499/