oracle 如何将 IN 运算符与 LIKE 条件相结合（或获得可比较结果的最佳方法）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2130907/
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
How to combine IN operator with LIKE condition (or best way to get comparable results)
提问by tputkonen
I need to select rows where a field begins with one of several different prefixes:
我需要选择字段以几个不同前缀之一开头的行：
select * from table
where field like 'ab%'
or field like 'cd%'
or field like "ef%"
or...
What is the best way to do this using SQL in Oracle or SQL Server? I'm looking for something like the following statements (which are incorrect):
在 Oracle 或 SQL Server 中使用 SQL 执行此操作的最佳方法是什么？我正在寻找类似于以下语句的内容（不正确）：
select * from table where field like in ('ab%', 'cd%', 'ef%', ...)
or
或者
select * from table where field like in (select foo from bar)
EDIT: I would like to see how this is done with either giving all the prefixes in one SELECT statement, of having all the prefixes stored in a helper table.
编辑：我想看看这是如何完成的，要么在一个 SELECT 语句中提供所有前缀，要么将所有前缀存储在帮助表中。
Length of the prefixes is not fixed.
前缀的长度不固定。
回答by Lieven Keersmaekers
Joining your prefix table with your actual table would work in both SQL Server & Oracle.
将前缀表与实际表连接起来可以在 SQL Server 和 Oracle 中使用。
DECLARE @Table TABLE (field VARCHAR(32))
DECLARE @Prefixes TABLE (prefix VARCHAR(32))
INSERT INTO @Table VALUES ('ABC')
INSERT INTO @Table VALUES ('DEF')
INSERT INTO @Table VALUES ('ABDEF')
INSERT INTO @Table VALUES ('DEFAB')
INSERT INTO @Table VALUES ('EFABD')
INSERT INTO @Prefixes VALUES ('AB%')
INSERT INTO @Prefixes VALUES ('DE%')
SELECT t.*
FROM @Table t
INNER JOIN @Prefixes pf ON t.field LIKE pf.prefix
回答by ghostdog74
you can try regular expression
你可以试试正则表达式
SELECT * from table where REGEXP_LIKE ( field, '^(ab|cd|ef)' );
回答by womp
If your prefix is always two characters, could you not just use the SUBSTRING() function to get the first two characters of "field", and then see if it's in the list of prefixes?
如果你的前缀总是两个字符，你能不能只用 SUBSTRING() 函数来获取“字段”的前两个字符，然后看看它是否在前缀列表中？
select * from table
where SUBSTRING(field, 1, 2) IN (prefix1, prefix2, prefix3...)
That would be "best" in terms of simplicity, if not performance. Performance-wise, you could create an indexed virtual column that generates your prefix from "field", and then use the virtual column in your predicate.
如果不是性能，那将是简单性方面的“最佳”。在性能方面，您可以创建一个索引虚拟列，从“字段”生成您的前缀，然后在您的谓词中使用虚拟列。
回答by Adam Musch
Depending on the size of the dataset, the REGEXP solution may or may not be the right answer. If you're trying to get a small slice of a big dataset,
根据数据集的大小，REGEXP 解决方案可能是也可能不是正确的答案。如果您想获取大数据集的一小部分，
select * from table
where field like 'ab%'
or field like 'cd%'
or field like "ef%"
or...
may be rewritten behind the scenes as
可以在幕后改写为
select * from table
where field like 'ab%'
union all
select * from table
where field like 'cd%'
union all
select * from table
where field like 'ef%'
Doing three index scans instead of a full scan.
执行三个索引扫描而不是完整扫描。
If you know you're only going after the first two characters, creating a function-based index could be a good solution as well. If you really really need to optimize this, use a global temporary table to store the values of interest, and perform a semi-join between them:
如果您知道您只需要前两个字符，那么创建基于函数的索引也可能是一个很好的解决方案。如果你真的需要优化这个，使用一个全局临时表来存储感兴趣的值，并在它们之间执行半连接：
select * from data_table
where transform(field) in (select pre_transformed_field
from my_where_clause_table);
回答by saurabh-ar
You can also try like this, here tmpis temporary table that is populated by the required prefixes. Its a simple way, and does the job.
您也可以这样尝试，这里的tmp是由所需前缀填充的临时表。这是一种简单的方法，并且可以完成工作。
select * from emp join
(select 'ab%' as Prefix
union
select 'cd%' as Prefix
union
select 'ef%' as Prefix) tmp
on emp.Name like tmp.Prefix
