使用SQL，您可以在WHERE LIKE子句中使用子查询吗？

I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement.

我甚至不确定如何在概念上听起来很奇怪,但我会尝试一下。基本上我正在寻找一种方法来创建一个本质上是WHERE IN LIKE SELECT语句的查询。

As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like:

例如,如果我想查找具有hotmail.com电子邮件地址的所有用户记录,我可以执行以下操作:

SELECT UserEmail 
FROM Users 
WHERE (UserEmail LIKE '%hotmail.com')

But what if I wanted to use a subquery as the matching criteria? Something like this:

但是,如果我想使用子查询作为匹配条件呢?像这样的东西:

SELECT UserEmail 
FROM Users 
WHERE (UserEmail LIKE (SELECT '%'+ Domain FROM Domains))

Is that even possible? If so, what's the right syntax?

这有可能吗?如果是这样,什么是正确的语法?

2 个解决方案

#1

You can try

你可以试试

SELECT  u.*
FROM    Users u INNER JOIN
        Domains d ON u.UserEmail LIKE '%'+ d.Domain

Or even try

甚至尝试

SELECT  u.*
FROM    Users u
WHERE   EXISTS(SELECT 1 FROM Domains d WHERE u.UserEmail LIKE '%' + d.Domain)

#2

Although

SELECT  u.UserMail
FROM    Users u
WHERE   EXISTS(SELECT 1 FROM Domains d WHERE u.UserEmail LIKE '%' + d.Domain)

will give what you look for, please realise that like is expensive and that you could shave off a bit of time with (mysql dialect):

会给你所寻找的东西,请意识到它是昂贵的,你可以用(mysql方言)刮掉一点时间:

SELECT  u.UserMail
FROM    Users u
WHERE   SUBSTRING_INDEX(u.UserMail, '@', -1) IN (SELECT d.Domain FROM Domains)

or even

SELECT  u.UserMail
FROM    Users u
        INNER JOIN Domains d ON SUBSTRING_INDEX(u.UserMail, '@', -1) = d.Domain

(and that you could split e-mail into username and domain fields if this is a common operation in your database)

(如果这是数据库中的常见操作,您可以将电子邮件拆分为用户名和域字段)

EDIT: I missed the MS SQL server tag. For that dialect

编辑:我错过了MS SQL服务器标签。对于那种方言

substring(UserMail, charindex('@', UserMail) + 1, len(UserMail) - charindex('@', UserMail) )

should outperform LIKE (because it will be performed once per row in Users and the you get to straight join, where the like approach will have to be performed for each value in Users on each row in Domains).

应该胜过LIKE(因为它将在用户中每行执行一次,并且您将进行直接连接,其中必须对域中每行的用户中的每个值执行类似的方法)。

P.S. check my formulas for start and length in substring (it was Friday night yesterday).

附:检查我的公式为子串的开始和长度(昨天是星期五晚上)。

#1