LIKE 使用返回多行的子查询 [英] LIKE using subquery returning multiple rows
问题描述
有 2 个表 emailinfo(id,email) 和 keywordinfo(id,keyword).emailinfo 包含 80,000 行，keywordinfo 包含 2000 行.
There 2 tables emailinfo(id,email) and keywordinfo(id,keyword). emailinfo contains 80,000 rows and keywordinfo contains 2000 rows.
我想要电子邮件信息表中不包含关键字信息表中关键字的电子邮件.
I want emails from emailinfo table which does not contains keywords from keywordinfo table.
我不希望多行条件
我已将所有关键字保存在keywordinfo表中
I have kept all keywords in keywordinfo table
现在我必须从 emailinfo 表中获取所有不包含 keywordinfo 表中任何关键字的电子邮件.
Now I have to fetch all the emails from emailinfo table which not containing any keywords from keywordinfo table.
我想要如下查询，
select email
from emailinfo
where email not like % (select keyword from keywordinfo)
我尝试过以下查询
SELECT email
FROM emailinfo
join keywordinfo on email like '%' + keyword +'%'**
但它没有给我正确的结果，而且速度很慢.
but its not giving me proper results and is very slow.
推荐答案
您的第二个查询稍有变化如何...?
SELECT Email
FROM EmailInfo
LEFT JOIN KeywordInfo ON Email LIKE '%' + Keyword + '%'
WHERE KeywordInfo.ID IS NULL
示例数据:
CREATE TABLE #EmailInfo (ID INT, Email VARCHAR(50))
INSERT INTO #EmailInfo (ID, Email) VALUES (1, 'test@example.com')
INSERT INTO #EmailInfo (ID, Email) VALUES (2, 'someone@sample.com')
INSERT INTO #EmailInfo (ID, Email) VALUES (3, 'testing@sample.com')
INSERT INTO #EmailInfo (ID, Email) VALUES (4, 'blahblah@blah.com')
INSERT INTO #EmailInfo (ID, Email) VALUES (5, 'example@email.com')
INSERT INTO #EmailInfo (ID, Email) VALUES (6, 'another@goodemail.com')
CREATE TABLE #KeywordInfo (ID INT, Keyword VARCHAR(50))
INSERT INTO #KeywordInfo (ID, Keyword) VALUES (1, 'sample')
INSERT INTO #KeywordInfo (ID, Keyword) VALUES (2, 'test')
SELECT Email
FROM #EmailInfo EmailInfo
LEFT JOIN #KeywordInfo KeywordInfo ON Email LIKE '%' + Keyword + '%'
WHERE KeywordInfo.ID IS NULL
DROP TABLE #EmailInfo
DROP TABLE #KeywordInfo
这篇关于LIKE 使用返回多行的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
