有没有办法在 SQL 语句中将 IN 与 LIKE 结合使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/610796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there any way to combine IN with LIKE in an SQL statement?
提问by Ziltoid
I am trying to find a way, if possible, to use IN and LIKE together. What I want to accomplish is putting a subquery that pulls up a list of data into an IN statement. The problem is the list of data contains wildcards. Is there any way to do this?
如果可能的话，我试图找到一种方法来同时使用 IN 和 LIKE。我想要完成的是将一个将数据列表拉入 IN 语句的子查询。问题是数据列表包含通配符。有没有办法做到这一点？
Just something I was curious on.
只是我很好奇的东西。
Example of data in the 2 tables
Parent table
ID Office_Code Employee_Name
1 GG234 Tom
2 GG654 Bill
3 PQ123 Chris
Second table
ID Code_Wildcard
1 GG%
2 PQ%
Clarifying note (via third-party)
澄清说明（通过第三方）
Since I'm seeing several responses which don't seems to address what Ziltoid asks, I thought I try clarifying what I think he means.
由于我看到几个回复似乎没有解决 Ziltoid 的问题，我想我试着澄清我认为他的意思。
In SQL, "WHERE col IN (1,2,3)" is roughly the equivalent of "WHERE col = 1 OR col = 2 OR col = 3".
在 SQL 中，“ WHERE col IN (1,2,3)”大致相当于“ WHERE col = 1 OR col = 2 OR col = 3”。
He's looking for something which I'll pseudo-code as
他正在寻找我将伪代码为的东西
WHERE col IN_LIKE ('A%', 'TH%E', '%C')
which would be roughly the equivalent of
这大致相当于
WHERE col LIKE 'A%' OR col LIKE 'TH%E' OR col LIKE '%C'
The Regex answers seem to come closest; the rest seem way off the mark.
正则表达式的答案似乎最接近；其余的似乎离题了。
回答by Shawn Loewen
I'm not sure which database you're using, but with Oracle you could accomplish something equivalent by aliasing your subquery in the FROM clause rather than using it in an IN clause. Using your example:
我不确定您使用的是哪个数据库，但是对于 Oracle，您可以通过在 FROM 子句中为子查询添加别名而不是在 IN 子句中使用它来完成等效的操作。使用您的示例：
select p.*
from
(select code_wildcard
from second
where id = 1) s
join parent p
on p.office_code like s.code_wildcard
回答by Quassnoi
In MySQL, use REGEXP:
在MySQL，使用REGEXP：
WHERE field1 REGEXP('(value1)|(value2)|(value3)')
Same in Oracle:
同样在Oracle：
WHERE REGEXP_LIKE(field1, '(value1)|(value2)|(value3)')
回答by kemiller2002
Do you mean somethign like:
你的意思是这样的：
select * FROM table where column IN (
SELECT column from table where column like '%%'
)
Really this should be written like:
真的应该这样写：
SELECT * FROM table where column like '%%'
Using a sub select query is really beneficial when you have to pull records based on a set of logic that you won't want in the main query.
当您必须根据主查询中不需要的一组逻辑提取记录时，使用子选择查询非常有用。
something like:
就像是：
SELECT * FROM TableA WHERE TableA_IdColumn IN
(
SELECT TableA_IdColumn FROM TableB WHERE TableA_IDColumn like '%%'
)
update to question:
更新问题：
You can't combine an IN statement with a like statement:
不能将 IN 语句与 like 语句结合使用：
You'll have to do three different like statements to search on the various wildcards.
您必须执行三个不同的 like 语句来搜索各种通配符。
回答by Mitch Wheat
You could use a LIKE statement to obtain a list of IDs and then use that in the IN statement.
您可以使用 LIKE 语句获取 ID 列表，然后在 IN 语句中使用它。
But you can't directly combine IN and LIKE.
但是不能直接将 IN 和 LIKE 组合起来。
回答by Tom H
Perhaps something like this?
也许像这样？
SELECT DISTINCT
my_column
FROM
My_Table T
INNER JOIN My_List_Of_Value V ON
T.my_column LIKE '%' + V.search_value + '%'
In this example I've used a table with the values for simplicity, but you could easily change that to a subquery. If you have a large list (like tens of thousands) then performance might be rough.
在本例中，为简单起见，我使用了一个带有值的表，但您可以轻松地将其更改为子查询。如果您有一个很大的列表（例如数以万计），那么性能可能会很差。
回答by Paul Morgan
select *
from parent
where exists( select *
from second
where office_code like trim( code_wildcard ) );
Trim code_wildcard just in case it has trailing blanks.
修剪 code_wildcard 以防它有尾随空白。
回答by Mike Woodhouse
If I'm reading the question correctly, we want all Parent rows that have an Office_code that matches any Code_Wildcard in the "Second" table.
如果我正确阅读了问题，我们希望所有 Parent 行的 Office_code 与“Second”表中的任何 Code_Wildcard 匹配。
In Oracle, at least, this query achieves that:
至少在 Oracle 中，这个查询实现了：
SELECT *
FROM parent, second
WHERE office_code LIKE code_wildcard;
Am I missing something?
我错过了什么吗？
回答by schooner
You could do the Like part in a subquery perhaps?
您可以在子查询中执行 Like 部分吗？
Select * From TableA Where X in (Select A from TableB where B Like '%123%')
Select * From TableA Where X in (Select A from TableB where B Like '%123%')
