T-SQL:[列]不喜欢[其他选择的任何值]

I have table shop with an Email column.

我有一个有电子邮件专栏的桌子店。

Also I have another table Unsubscribed with a EmailPattern column.

另外，我还有一个未订阅的EmailPattern列的表。

How I can select only shops with Email column does not match any EmailPattern value from Unsubscribed table?

如何才能只选择电子邮件列与未订阅表中的电子邮件模式值不匹配的商店?

Example:

例子:

Unsubscribed has these records:

没订阅这些记录:

aaa
bbb
ccc

I expect result like:

我期望结果:

select * 
from Shop 
where Email not like "%aaa%" 
  and Email not like "%bbb%" 
  and Email not like "%ccc%"

2 个解决方案

#1

Select *
From shop
Where not exists (
  Select 1
  From unsubscribed
  Where shop.email like '%' + unsubcribed.emailpattern + '%'
)

#2

This will work:

这将工作:

-- setup test tables
CREATE TABLE #Shop (ID INT, Email VARCHAR(100));
CREATE TABLE #Unsubscribed (EmailPattern VARCHAR(100));

-- fill with example data
INSERT #Shop VALUES (1,'test@a.com');
INSERT #Shop VALUES (2,'david@r.com');
INSERT #Shop VALUES (3,'user@r.com');
INSERT #Shop VALUES (4,'george@usa.com');
 -- general phrase
INSERT #Unsubscribed VALUES ('test');
 -- specific email
INSERT #Unsubscribed VALUES ('user@r.com');
--INSERT #Unsubscribed VALUES ('@'); -- watch out, this will match every email!
--INSERT #Unsubscribed VALUES ('_'); -- so will this

-- filter results:
-- This returns rows from Table A (Shop), when they do not match anything in table B (Unsubscribed)
SELECT
    A.*
FROM
    #Shop A
    LEFT JOIN #Unsubscribed B ON A.Email LIKE '%' + B.EmailPattern + '%'
WHERE
    B.EmailPattern IS NULL;

DROP TABLE #Shop;
DROP TABLE #Unsubscribed;

Be aware that if certain phrases or characters get into the unsubscribe pattern, It can potentially match every record!

请注意，如果某些短语或字符进入退订模式，它可能会匹配每个记录!

#1

Select *
From shop
Where not exists (
  Select 1
  From unsubscribed
  Where shop.email like '%' + unsubcribed.emailpattern + '%'
)

#2